I'm building a database for a local charity. The database will be filled with entries coming from different sources and updated daily.
One of the element (e.g. a car) has a relationship with many elements (e.g. car colors).
One of my friends who is helping with the project suggested to use a JSON field. I'm not a DBA and maybe I don't fully understand the benefit of using JSON over a many to many table.
I could build the cars table as follow:
Where an entry would be:
Cars
+----+--------+-------------------------------+
| id | name | colors |
+----+--------+-------------------------------+
| 1 | Fiesta | { "15": "red", "22": "blue" } |
+----+--------+-------------------------------+
Or I could build a many to many table:
Where an entry would be:
Cars
+----+--------+
| id | name |
+----+--------+
| 1 | Fiesta |
+----+--------+
Colors
+----+------+
| id | name |
+----+------+
| 15 | red |
+----+------+
| 22 | blue |
+----+------+
CarsColors
+----+-------+---------+
| id | carID | colorID |
+----+-------+---------+
| 1 | 1 | 15 |
+----+-------+---------+
| 2 | 1 | 22 |
+----+-------+---------+
Personally I find the second approach would be cleaner, especially since in future we'll need to perform operations like "find all cars with color red". I know that the answer probably is "it depends" but, please, could you point me in the right direction?
Best Answer
"find all cars with color red" is a relational question, and therefore a normalized relational database with the table structure you designed in your second example makes more sense than JSON. JSON makes things easier for highly variable and structureless scenarios, but when you have relationships between objects (entities) like
CarsandColors, then it's the "lazy developer solution" to use JSON instead of a relational database. And it's more work in the long run when you have to ask relational questions like your example, when the data is stored in JSON as opposed to a relational structure with normalized tables.