Database design for user-item relationships

Question

I'm making a website to sell widgets. I have a table for widgets, each of which have a few attributes (widget name, style, etc). I also have a table for users. Widgets have unique IDs, and users also have unique IDs associated with them.

My users want to start "adding" widgets to their account. Each widget can be added to an infinite amount of users. So my question is should I make a table, lets call it User_Widget, that relates UserIDs to WidgetIDs?

Another method I was thinking of is to have a column in the User table with comma separated values (WidgetID1xQuantity, WidgetID2xQuantity, etc). If I standardize this format, I can use php to parse this and implode these into an array, which can then be cross-referenced to pull info from Widget table.

Which one is faster/better practice? If I use the User_Widget method what happens if more than one user adds the same widget? Will I just have two entries? Does this not add extra overhead?

Widget | User wid1 | user1 wid1 | user2

Would searching through these (using JOIN, etc) be slower than the other method?

Thanks in advance for the help.

Daniel.

Answer 1

Definitely a new relation in a specific table.

Apart from the other drawbacks in the previous comments, remember that:

1) Assuming that the widget has some information linked to it, you still need to access the DB to get the widget data, so you are not actually saving much (and as others pointed out, parsing the CSV is cumbersome) 2) Forget doing stuff like "please list alla users having X but not Y and Z in their widget collection" - or at the very least forget doing it efficiently. 3) Same for "Select all users that have the same widgets owned by User Z" 4) What if you need to massively replace widget X with Widget Y? Or give everyone who has W a free Z?

There are plenty of scenarios where the dedicated table gives you easily described SQL queries, efficiency, compactness. And little (or no) cases where the CSV field "wins".

Answer 2

I recommend new table with relations
It will works faster