Reputation: 1729

SQL table logic, which way is correct

I am having an argument with myself over the best way to do this.

Here is the scenario.

I have a table that has a list of users in it.

For simplicity let's say it looks like this:

Mike | male
Amy  | female
Andy | male

and so on.

I then have a list of colours, let's say 4 colours:

red
blue
green
yellow

any of the users can have one or more of the colours assigned to them.

Do I add a new column to the users table called assignedColours, create a new table called colours that looks like this:

id   |   colour
1    |   red
2    |   blue
3    |   green
4    |   yellow

and then assign then store an array in the users assignedColours coloumn like

Mike   | Male   | 2,3
Amy    | Female | 1,3,4

Or do I create a colour table with columns of the colours and assign the users to that column like:

Red     |   Blue    |   Green    |   Yellow
        |   Mike    |   Mike     |   
Amy     |           |   Amy      |   Amy

or is there a better way of doing this all together?

I am looking for an answers as to which one is the preferred way and why.

Upvotes: 2

Answers (4)

Mark

Reputation: 5787

Why not creating an adjacency table? It will allow for easier joins and setting foreign keys.

Example:

Users

id| name | gender
-----------------
1 | Mike | male
2 | Amy  | female
3 | Andy | male

Colors

id | name
1  | red
2  | blue
3  | green
4  | yellow

UserColors

user_id | color_id
1       | 3
1       | 2
2       | 1
4       | 2

The UserColors table allows you to associate the Users with the Colors. The concept is known as adjacency table or join table and is used to map one2many and many2many relations.

You can find a more developed example here: http://www.joinfu.com/2005/12/managing-many-to-many-relationships-in-mysql-part-1/

Upvotes: 3

Itai Sagi

Reputation: 5615

Well, the idea behing RDS (Relational Data Store) such as MySQL, is to have the data normalized and thus, easily searchable.

Because of that, your best bet, is to save a table of colors, a table of users and a many-to-many table, saving the users-colors, their definition would be something along this line

users table

id | int
name | varchar
gender | varchar

colors table

id | int
name | varchar

users_colors table

id | int
user_id | int
color_id | int

this way, you can easily find all users having a certain color, whereas with an un-normalized scheme, you would run into a problem, how would you query for users with a certain color and not another?

Upvotes: 3