Hi everyone!
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?

Thanks in advance!

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).

shammat, thanks a lot! EAV is really what I need. I'm just going to enhance it a bit, in order to store different datatypes.

Just keep in mind that EAV will be a nightmare if you plan to do reports on those attributes or let the user search on them. The EAV pattern usually doesn't scale very well here.

Enhancing EAV. I hear the sound of approaching thunder...

And I think I know where the lightning will hit
Dave

Actually I do need those attributes searchable. And comparable. And so on. Thats why I want to create different tables for different datatypes. attr_int, attr_bool, attr_char etc.

Looks like I don`t know something Am I doing something wrong? Are there any other methods of storing sparse matrix?

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'...

If I had to use an EAV approach I would use a single table with a single "key" column and multiple columns for different datatypes (and possibly one column indicating the datatype to be used)

with EAV, it's not the storing of the data that's hard, it's the extraction

have you ever written any SQL to pull information out of an EAV database?

you should seriously try it first, it will open your eyes, tedd

shammat, sounds really interesting! Your approach is definitely more convenient than mine. Now I`m going to play around with data, as r937 suggested.

Try to retrieve something like the following:

- 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?

here come da lightning fo sho

Gonna do it. Have no options anyway