CODEWITHSUNDEEP
mysql
TDave00
TDave00

Reputation: 374

Better to store multiple values in a table column, or create new table?

I have a user table that stores data about the user. Let's say one of those things I need to store is about all the widget manufacturers that the user uses. I need to get data about each of those manufacturers (from the manufacturers table) to present to the user. In the user table should I store the users manufacturers as multiple values in a column like below and then split it out to create my query?

user

id  | username  | password | widget_man_id | etc
1   | bob       | ****     | 1,4,3         | some data
2   | don       | *****    | 2,3,1         | some more data
.
.

Or should I create a new table that holds all users information about the manufacturers they use? Like so...

user_man

id  | manufacturer  |  user  |  order
1   | 1             |  1     |  1
2   | 4             |  1     |  2
3   | 3             |  1     |  3 
4   | 2             |  2     |  1
5   | 3             |  2     |  2
6   | 1             |  2     |  3
.
.

and then perform a query like this for a given user...

select manufacturer, order from user_man where user=1

**ALSO, how could I sort the results in descending order based on the order field?

Upvotes: 0

Views: 1356

Answers (4)

Roger
Roger

Reputation: 348

The common approach would be to have a table user (like yours but without the widget_man_id):

user

id  | username  | password | etc
1   | bob       | ****     | some data
2   | don       | *****    | some more data

A manufacturer table:

manufacturer 

id  | name  | etc
1   | man1  | some data
2   | man2  | some more data

And a many-to-many relationship table (like your example):

user_man

id  | manufacturer  |  user  |  order
1   | 1             |  1     |  1
2   | 4             |  1     |  2
3   | 3             |  1     |  3 
4   | 2             |  2     |  1
5   | 3             |  2     |  2
6   | 1             |  2     |  3

Upvotes: 1

Silver Quettier
Silver Quettier

Reputation: 2050

What you are trying to implement here is a Many-to-Many relationship between your Users and Manufacturers.

The only good way to do this in most RDBMS, such as MySQL, is through a Junction Table. Although it does not reflect a particular object of your model, it will help modeling the relationships between the two others.

Create a third table, and do not forget to create Foreign Keys so you can prevent errors while updating or inserting records, and use some interesting features such as cascade deletion.

Upvotes: 3

Your Common Sense
Your Common Sense

Reputation: 157828

Separate table is the only the way to go.

To order your results in descending order just add DESC keyword

SELECT manufacturer FROM user_man WHERE user=1 ORDER BY `order` DESC

Upvotes: 1

Tom
Tom

Reputation: 6663

Do not put multiple values into a single column and then split them out. This is much slower for querying as each of the values cannot be indexed. You are much better off having a table with a separate record for each one. You can index this table and your queries will run much faster.

Upvotes: 5

Related Questions