5

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:

enter image description here

Where an entry would be:

Cars
+----+--------+-------------------------------+
| id |  name  |             colors            |
+----+--------+-------------------------------+
|  1 | Fiesta | { "15": "red", "22": "blue" } |
+----+--------+-------------------------------+

Or I could build a many to many table:

enter image description here

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?

2
  • 2
    It depends... ;) and it is probably also opinion based... But for the record: I would be opposed to use JSON for this, if it broke the first normal form. So unless the JSON can be seen as atomic in the context of the DB, i.e. the bits and pieces it stores aren't of any concern for the DB, I'd always try to go the relational way, so that relational tools can easily be used.
    – sticky bit
    Dec 27, 2020 at 18:42
  • Explain how a car (singular) can have many colors. A manufacturer can offer a particular model of car (vehicle) in many different colors, but that vehicle (once produced) is of a single color. I think your model / modeling is off here but only you know what you mean by "car".
    – SMor
    Dec 27, 2020 at 22:55

3 Answers 3

3

"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 Cars and Colors, 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.

2

json is ok when you don't need to change it, as all programing languages can handle it.

the normalized version is more flexible and easier to update, as it is the natural form fr rdms. A json especially when you need only single pr few information for it, needs always to be extracted, which makes it more complex to handle.

So the vote goes to the normalized Version.

1

Depends. If you are newer gonna ask/query: "Which car is be red" and will only quety "what colors is fiesta" Then JSON may be ok, in case the amount of data is managable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.