Table Design/Optimization Question.
Posted 30 June 2006 - 03:56 PM
Would it be better to?
Have one table for users and another table for items and each time a user adds an item to their list, add that users name and item to the items table?
Set it up so that each time a user registers, a separate table is created just for them and then each time an item is added to that user’s list it is added to their own personal table.
Have one master table that has the users in it and then a field for items that is a list built with some type of delimiter and has to be destructed and constructed each time a user deletes or adds an item to their list.
Or is there a better way that I am simply to inexperienced to see yet?
I’d love to hear some of the different ideas from experienced users with respect to performance positives and negatives for the different design options.
Thanks in advance for any feedback!
Posted 30 June 2006 - 04:02 PM
user_id, username, etc.
item_id, item_name, etc.
combined_id, user_id, item_id
then just link them together. You may want to add additionaly fields to the combined table if you want to say group items that a user entered on a certain day.
Posted 30 June 2006 - 07:32 PM
Posted 30 June 2006 - 09:15 PM
In the database I work on we have about a years worth of data in the new structure I moved us to and some of the relational tables have over 45,000 rows in them. I do this for work... my company has around 1600 products in the relational database and we use it to track all of our orders, do invoices and all that kinds of stuff. I have had no problems with speed, although it will really come down to a matter of how well your database is optimized... both with respect to your system (memory allowances and such) and indexes.
I use a lot of explain queries to test and refine my queries. You really want to make sure that when you run a multi-table query and each table has 30,000+ rows that it isn't checking any more rows than absolutely necessary
Posted 03 July 2006 - 05:11 AM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users