Jump to content

Table Design/Optimization Question.


cetaces

Recommended Posts

I have a design question that perhaps someone could guide me on.  If I am to build a database consisting of users and a list of items attached to each user what would be the best design layout.  There can be only one of each particular item per user but there may be any number of different items assigned to a user. 
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?

User1 item1
User2 item1
User3 item4
User1 item2
User1 item4
User2 item2
Etc…

Or
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.

Or
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!
Link to comment
Share on other sites

have a table that stores users

Users [table][tr][td]
user_id, username, etc.

Items [table][tr][td]
item_id, item_name, etc.

Combined [table][tr][td]
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.
Link to comment
Share on other sites

Guest Stephen Carroll
What happens when the combined table starts to grow large.  For example if the table gets into the thousands of users and each user has hundreds of items, does this start to diminish performance?  At what size does a Mysql table become so large that query speed is affected?
Link to comment
Share on other sites

when you are designing a relational database (like I suggested) it really isn't a big deal if you make effective use of indexes.

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 ;)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.