Let say I have a MySQL database with a user table like this:
|ID| Name| email |...
|1 | John| john@doe.com|...
I will have a php page where the user will choose with help of checkboxes, between different options in two independent interest areas. For this example lets say: favorite fruit and cars.
Fruits: Apple, Lemon, Pear, Banana. Cars: Ferrari, Mercedes, Volvo, BMW.
Now I have two possibilities:
1.) Creating a column for each fruit and each car associated to each user, something like this:
|ID| Name| email |apple | lemon | pear | banana | ferrari | mercedes | volvo | BMW |
|1 | John| john@doe.com| 1 | 1 | 0 | 1 | 0 | 1 | 0 | 1 |
2) Creating two new tables called: fruits and cars.
|ID| Name |
| 1| Apple |
| 2| Lemon |
| 3| Pear |
| 4| Banana|
|ID| Name |
| 1| Ferrari |
| 2| Mercedes |
| 3| Volvo |
| 4| BMW |
Then creating a relational table called user_fruit and another called user_cars. E.g:
|ID_user| ID_fruit |
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
(Note that fruit id 3, Pear does not appear as it not selected --> 0)
|ID_user| ID_car |
| 1 | 2 |
| 1 | 4 |
Question: I don't know which approach is better? The first one is easy at first hand, but once the options grow, the table will have to many tinyint columns. The second one is more difficult at the beginning with all the relations, but is easier to maintain, except when a user uncheck one option, that I don't fell very comfortable deleting rows by php, specially on index based tables.
I don't know if someone has faced some similar task and can give me some suggestions.
I don't really need any code example, as I know how to develop both options, is more a performance and maintenance approach that I'm looking for.
Cheers!
Aucun commentaire:
Enregistrer un commentaire