Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2010
    Posts
    25

    Question How to store a data with unknown amount of attributes?

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    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).

  3. #3
    Join Date
    Nov 2010
    Posts
    25
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Quote Originally Posted by tedd View Post
    shammat, thanks a lot! EAV is really what I need.
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Enhancing EAV. I hear the sound of approaching thunder...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    And I think I know where the lightning will hit
    Dave

  7. #7
    Join Date
    Nov 2010
    Posts
    25
    Quote Originally Posted by shammat View Post
    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.
    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.

    Enhancing EAV. I hear the sound of approaching thunder...
    And I think I know where the lightning will hit
    Looks like I don`t know something Am I doing something wrong? Are there any other methods of storing sparse matrix?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Quote Originally Posted by tedd View Post
    Thats why I want to create different tables for different datatypes. attr_int, attr_bool, attr_char etc.
    One table for each datatype?
    Now that sounds very wrong.

  9. #9
    Join Date
    Nov 2010
    Posts
    25
    Quote Originally Posted by shammat View Post
    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'...

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    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)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tedd View Post
    Looks like I don`t know something Am I doing something wrong?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2010
    Posts
    25
    shammat, sounds really interesting! Your approach is definitely more convenient than mine. Now I`m going to play around with data, as r937 suggested.
    Last edited by tedd; 11-06-10 at 08:27.

  13. #13
    Join Date
    Nov 2003
    Posts
    2,933
    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?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    Try to retrieve something like the following:
    here come da lightning fo sho
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2010
    Posts
    25
    Quote Originally Posted by r937 View Post
    here come da lightning fo sho
    Gonna do it. Have no options anyway

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •