I am creating a sql table users there are three columns id,username, favourite colours.user favourite colours maybe more than one like red,blue.how to store these values in favourite colours column.i am using mysql.
Asked
Active
Viewed 99 times
0
-
Two possible choices : 1. consider storing the favorite_color in a separate table, linking to the user table by a key 2. store the favorite colors in a single data field -- colors separated by commas (For sure 1 is better from a db design perspective.) – Ken Lee Mar 09 '21 at 06:17
-
@KenLee 2 is not an option at all. Or, like, shooting yourself in a foot is an "option" from this perspective – Your Common Sense Mar 09 '21 at 06:28
-
@KenLee: never store comma separated values. If you really **have to** de-normalize a model (assuming there are valid reasons to to do so) choose at least a "structured" type like JSON. But indeed: in this case a properly normalized model with a one-to-many relationship is the better solution – Mar 09 '21 at 06:28
-
@a_horse_with_no_name That really depends whether this is a homework exercise or a practical one. The OP tagged this question 'php', and php has facilities for manipulating JSON, as I point out in my answer below. – Darwin von Corax Mar 09 '21 at 06:33
-
@DarwinvonCorax: JSON is an acceptable solution to de-normalization. Storing comma separated values never is. – Mar 09 '21 at 06:36
-
@a_horse_with_no_name That's true. My point was that denormalization might be acceptable in this instance, unless normalization is the *point* of the exercise. – Darwin von Corax Mar 09 '21 at 06:42
1 Answers
0
The by-Codd correct approach would be to split off favourite colour into a separate table. The columns would be id and colour, the primary key would be both columns combined, and id would be a foreign key referencing users.id. It might also be useful to put a non-unique index on favourites.id for performance, but that would be an optimization exercise.
The other option would be to make favourite_colour a JSON column and store the colours as a JSON array; for a small exercise like this that might be simpler, as you could use PHP's JSON facilities to manipulate it.
Darwin von Corax
- 5,201
- 3
- 17
- 28