Table Design/Optimization Question.

[cetaces]

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!

[jworisek]

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.

[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?

[jworisek]

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 ;)

[fenway]

Until you get to 100K of rows, I wouldn't be concerned... performance is usually limited by poor index choice or improper queries, not by the underlying size of the table itself.