Two options - which one is better?

[mobei]

Hi folks, first post of many probably!

 

This is pretty straightforward but I'm just not sure which would be the best option.  Basically I need to store some information on a user and various ID's of items that that user has selected.  So here's the options I was thinking of going for, with option 1 being the one I initially thought would be best but possibly unnecessary:

 

1) User parent table links to items table through user-items table with many (hundreds) of rows per user.  This small table will just have the user id, the item id, and the item level stored (some items can have the same id, but no two will have the same id and level).

This representation will call for many inserts/deletes as users pick and choose different items.

 

2) Single User table where we store 3 columns for the 3 different item levels.  Each row in this stores all the selected item id's as a comma separated string for each of the 3 levels.  There will be no inserts/deletes but many updates on these values.  Each row will probably exceed 255bytes.  When doing a select on items, could just pull out this string and use in a where ... in clause.

 

Or that's the way I think they'd both work anyway!  So basically I was just wondering which one would be the preferred method for space/efficiency...?

 

Thanks in advance for any replies.

[roopurt18]

If you do not need to perform searching or mass updates on the items themselves, then I'd just store a serialized PHP object in the users table in a single text column.  I do this often with things like configuration since asking the question, "How many users prefer a blue background?" does not come up very often.

 

If you do[/i] need to perform searching or mass updates on the items then you're better off with your first choice.  Examples of questions that could lead to this situation are:

Which users have the same item as I do (and maybe the same level)?

Replace item #19283 with item #2919239 for all users

Increment or decrement the item level without having to pull anything out of the database

Does a certain item occur too often?

 

When doing a select on items, could just pull out this string and use in a where ... in clause.

WHERE ... IN( ... ) does not work like that.