And So I have these automobiles, various sorts of them. All sorts features its own table, with lots of characteristics. Then I've got a "automobiles" table, which supports the ids and kinds of all of the automobiles in each and every table. Automobiles might have photos, so I've got a "photos" table, having a vehicle_id, connecting the photo towards the vehicle it goes to.
Now every vehicle also offers a listing of apparatus it's, that we store like a JSON array in one "equipment" column.
Something similar to this: http://pastie.org/353195
How could I actually do this better, particularly with the gear column?
I'd come with an equipment table, after which possess a join table between your vehicle table and also the equipment table by having an equipment_id along with a vehicle_id.
This provides the many-to-many relationship between automobiles and equipment.
This makes it much simpler to question for, and check for, automobiles that have specific equipment, as well as your equipment data will not be repeated throughout your JSON packets. You may also make changes to kinds of equipment without needing to update plenty of JSON packets.
Storing JSON (or XML) in databases is generally bad, unless of course there's something natural concerning the application so that the information that should be extensible.
Your design is very okay aside from the gear column. I'd recommend getting a brand new table with id and title after which a table connecting the automobile towards the equipment it's.
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
your equipment table need to look a lot more like your photos table. A vehile_id column as well as an equipment column. Your client code can place the equipment right into a JSON array (or other things is needed). You normally shouldn't store things in JSON Arrays (or other format) inside your database.
For it one step further you may make a tool table then produce a many-to-many relationship between automobiles and equipment.
Why don't you make equipment a table such as the relaxation? Querying tools because it is is going to be hard, you will not have the ability to get it done using SQL alone, will invariably take some client application to deserialize it.