SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Various number of attributes

    What route is best to go if I need design tables for entities with arbitrary numbers of attributes? I know that is called EAV (entity - attribute - value) and tried a Google, but missed answer for actual table design.

    If we have arbitrary attributes with various data types, there are three ways to go:

    1. item_id - attribute - value // one table, values with different data types in third column
    2. item_id - attribute - value // one table for each data type
    3. item_id - attribute - string - integer - float - date ... //one column for each data type (I see this in BEA WebLogic)

    First option is terrible. But the others look fine (?). Downsides of second choice include complicated selects and writing, third choice leads to "Swiss cheese" table.

    Has anybody experiences with this issue?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    make every effort ~not~ to end up with an EAV design


    perhaps these articles can alert you to some of the pitfalls

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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