How to store a data with unknown amount of attributes?
Got a question. I need to design a DB schema for an auto-related project. This DB will store plenty of data about cars. And there is a tricky task: we do not know, and will never know, the actual amount of characteristics we need to store about each car. Therefore, I can`t hard-code data`s attributes into DB schema. So, I think, there will be a table called “Cars attributes” where I will enumerate all cars attributes, and a table called “Car to car attribute” where actual data will be stored. But there is another problem - attributes could be of different types: integer, strings and dictionaries. (Example of dictionary: “Types of engine” with possible values: “V6”, “V8” and so on. Attribute can possess only one of these values). So, I suppose I need to create at least three tables: “Car attributes integer”, “Car attributes string”, “Car attributes dictionaries”.
And the question is - am I right? Is there an another way to store the data with unknown amount of attributes? Can you suggest some reading about this topic?
You can use something called the "Entity Attribute Value" pattern (Search this forum, this has been discussed several times, and your favorite search engine will also return several hits)
There are also DBMS specific solutions to this problem.
In PostgreSQL you could use a hstore column to store key/value pairs. This is also quite efficient when searching as the hstore columns can be indexed
In Oracle, DB2 (and probably SQL Server) you could use a XML Column that contains these mappings. Searching will need to be done using XPath expressions and XQuery. Those can be indexed for Oracle and DB2 (and I think SQL Server as well).
One table for each datatype?
Now that sounds very wrong.
Hmm. Whats wrong with that? As for me, it is more convenient to create different tables, than to use only one table for all values. I could index those tables, I could perform comparisons on values in 'integer table', I could store large text chunks in 'text table'...
- Show count of cars that have a V6 engine for each manufacturer
- What's the average horsepower for a given engine type?
- How many cars have 4 cylinders but less than 100hp and are built by a specific manufacturer?