how to allow your code to store undetermined number of columns ?

Question

Hi, I think my question might be unclear , but i would try to explain it by example .

say that we had about 100 different car model , clearly all of the car would share common parts or specification but not all parts are shared between all of these 100 car brands

what is the best practice of storing these specification in this case ??

my idea was to store the most common specification in each column and the undetermined ( the rest )specification could be saved or serialized array as object or array .

doctrine simplify this operation by these datatypes (array-object) do you think this is good idea or could you please share with me your experience

here is my idea in 2 simple tables

Table 1
|-------|--------|-------|-------|-------|
| Id    | brand  |engine |desiel | blah..|
|-------|--------|-------|-------|-------|
| 1     | old car|   1.6 | yes   | blah..|
|-------|--------|-------|-------|-------|


Table 2 
|-------|--------|----------------------|
| Id    | car_id |  un common info      |
|-------|--------|----------------------|
| 1     |    1   |array of informations |
|-------|--------|----------------------|

i think my idea bad because it breaks the search ability

thanks for help !

Answer 1

Possibly want you need an Entity-Attribute-Value (EAV) schema. These can let you have records with varying amounts of information, but can be tricky to query.

Answer 2

A commonly used solution is to have a table that accepts key-value pairs (commonly called EAV):

|-------|--------|-------|
| carId | name   | value |
|-------|--------|-------|
| 1     | engine | 4cyl  |
|-------|--------|-------|

Answer 3

If you have to stick to a MySQL then as Paul and Netcoder says, EAV would be the solution to opt for.

Unless managed carefully EAV does have scalability issues however and your use case sounds like it would be better solved by a NoSQL (non-relational database) solution such as Couch or Mongo.

Document-orientated databases such as these are built surrounding the logic that an entity has no fixed number of fields, for example a product and the data about all of its images would reside within one 'document'.

Answer 4

Is forcing the number and types of columns certainly impossible? Even if you allow for a few vague ones? If you could determine the columns, no complex solution would be needed. Might be the least painful option. Just a thought.

Answer 5

Use one table for each unique combination of attributes but also put the common attributes into tables that are shared between the all the car types having those attributes in common. The idea is to ensure that every possible tuple describing a car can only appear in one place in the schema. There is a name for this principle: The Principle of Orthogonal Design.