Best design for multi-value objects

Question

Say that you have an object similar to a car for which you receive detailed information. The car you want to capture is painted red, black and white. The front tires are Bridgestone 275 / 35-18, and the rear are 325 / 30-19. And sometimes you can have only two tires (yes, it will be considered a motorcycle, which is a type of vehicle), and sometimes 18 tires, which can be different. Then there are a few fields that always differ from the size of the engine (if we let our imagination run, we can think of multi-motor vehicles, but I try to make it simple).

Our current strategy to solve this problem is to have a table for each of the fields, which can have multiple values. This will cause a large number of tables (we have a bunch of different objects with this requirement) and smell a little. This is the best strategy, and if not, what would be better?

Answer 1

If this is an opportunity for your application, you can look in couchdb .

Answer 2

If you use a relational database, your suggestion is almost the only way to do this. the theory of normal forms will give you more information about this - Wikipedia articles about this are not bad, albeit a bit heavy, because it is a complex theoretical subject when you get to higher levels of normalization. Common examples are common sense.

Assuming you have a car table, a color table, and a TyreType table (sorry for the English spelling), you apparently define a VehicleTyre and VehicleColour table, which acts as a join between the corresponding pairs of tables. This structure is actually quite healthy. It not only encapsulates the information you need directly, but also allows you to naturally fix things like, for example, which tire (for example, the front left - Bridgestone 275 / 35-18), or which part of the car is painted red (for example, with using the percentage field in the VehicleColour table).

You may want to model an object such as a vehicle that can determine the number of tires. Although this is not necessary in order to get working SELECT queries from the system, it will probably be useful both in your user interface and in determining how many buses to insert into your tables.

My company has many schemes that work on this basis. In fact, our object-relational structure automatically creates them to manage many-to-many relationships (and sometimes one-to-many relationships, depending on how we model them). Some of our applications have over 150 objects and over 100 of these join tables. There are no performance issues and no significant effect on data manageability, except that several table names are annoyingly long.

Answer 3

You are describing Star Schema . I think its pretty standard practice in your case

Edit: in fact, your scheme is slightly modified from the Star scheme, you use the primary key of the fact table in each of the dimension tables to join, so you can have several colors of paint, etc. In any case, I think this is a good way to deal with your organization. You can go one step further and normalize the size tables, and then you will have a snowflake pattern

Answer 4

It looks like you can look at something called the Hierarchical Model .

Or maybe a simple list of pairs (attr, value)?

Answer 5

If you are using SQL Server, don't be afraid to store the XML Data Type . I found that it makes such things a lot easier, a lot easier.

Answer 6

It really depends on whether only variables have only one variable (for example: you can have a variable number of tires of the same type or a certain number of tires that have a variable type).

Since you seem to need to have several variables (for example, a specific type for each tire with a variable number of tires), I am afraid that the best solution would be to have specific tables for each specific area of ​​the car that you want to customize.

If you have fields that simply have a set of values ​​to choose from (for example, 2, 4, or 6 windows), you can simply use an enumeration or define a new type of field using custom domains (depending on which DBMS you use).

Answer 7

Your current strategy is correct. You track so many kinds of data, so you need many tables. That's how it is. Does the DBMS complain?