0

I'm having trouble designing a database for a project I was working on recently. It's to do with a garage management system.

So I've realised I need to store multiple types of parts (Tyres, engines, wheel bases, this list needs to grow), with multiple types of details, some that each part shares (name, part number, condition, quantity) and some that are unique to each part (engines have weight and power, tyres have size and alloy type) and that also needs to grow too. This can't be limited to just 2 kinds of unique information, it needs to be infinite, so that more can be added at a later date if required

How can I design a database that is capable of storing all of this information without having tables for each specific part, so a table for tyres, a table for engines etc? I want to keep it so the least amount of tables as possible is used, so once designed, no new tables are added as new data is added.

Any help is appreciated.

0

so

This is what I've designed as a solution for this problem but I don't think it's right, due to the tables being linked together in a circle.

0

One possible way of doing this is to add a text field for non-shared properties, and them is a serialized array format, or a JSON string [Possibly in a separate table]. This comes with a problem though that you cannot index the "keys", so the search will be slow if the table grows big.

For example:

parts(id, name, quantity, ...) #eg. (1, 'Engine', 4, ...)
other_spec(part_id, specs text) #eg. (1, '{wight:500, power:60...}')

If you're open for alternatives, you may look at "document store" type of DBs, like MongoDB.

  • Yes that was a route I wanted to go through but I decided against it due to the need to edit all parts of the database at all time, and that model would have redundancies in it when lets say engines don't need to be defined by their power, and you'd need to go through each record to check there isn't any redundancies and it would just become a mess. – user2177940 Jul 28 '15 at 18:08

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