Someone tell me why my database design is stupid, and how I can fix it

Question

So I have these vehicles, many different types of them. Every type has its own table, with many attributes. Then I have a "vehicles" table, which holds the ids and types of all the vehicles in every table. Vehicles can have photos, so I have a "photos" table, with a vehicle_id, linking the photo to the vehicle it belongs to.

Now every vehicle also has a list of equipment it has, which I store as a JSON array in a single "equipment" column.

Something like this: http://pastie.org/353195

How could I do this better, especially with the equipment column?

Answer 1

I would have an equipment table, and then have a join table between the vehicle table and the equipment table with an equipment_id and a vehicle_id.

This gives you the many-to-many relationship between vehicles and equipment.

This will make it much easier to query for, and search for, vehicles which have specific equipment, and your equipment data won't be repeated throughout your JSON packets. You can also make changes to types of equipment without having to update lots of JSON packets.

Storing JSON (or XML) in databases is normally bad, unless there is something inherent about the application such that the data that needs to be extensible.

Answer 2

your equipment table should look more like your photos table. A vehile_id column and an equipment column. Then your client code can put the equipment into a JSON array (or whatever else is required). You typically should NOT store things in JSON Arrays (or any other format) in your database.

If you take it a step further you can make an equipment table then create a many-to-many relationship between vehicles and equipment.

Answer 3

Why not make equipment a table like the rest? Querying equipments as it is will be hard, you won't be able to do it using SQL alone, will always need some client app to deserialize it.

Answer 4

Your design is quite okay except for the equipment column. I'd recommend having a new table with id and name and then a table linking the vehicle to the equipment it has.

vehicles:
id | type
---------
1  | tank
2  | car
3  | boat
4  | car

equipment:
id | name
---------
1  | radio
2  | abs

vehicles_ equipment:
vehicles_id | equipment_id
2           | 1
2           | 2