Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    1

    Question What is the difference between these database structures?

    I'm looking for a resource describing the difference between some database structures. I can describe the structures but I don't know what the term is for each practice so it has proven impossible for me to find anything by searching.


    Design One: A single table with MANY columns. This is a simple setup but can get crazy if a lot column are necessary and can get wasteful if a lot of them are NULL. IE:

    Code:
    SomeTable(primarykey, somevarcharA, somevarcharB, ... somenumberA, somenumberB, ... )


    Design Two: A table with some data paired with another table containing a row for each of the attibutes. WordPress does this with their posts. IE:

    Code:
    SomeTable(primarykey, somevarcharA, somevarcharB)
    SomeTableAttributes(sometableforeignkey, somenumbername, numbericalvalue)


    Design Two (further): Additional tables for each datatype. Seems odd to have such a small 'SomeTable'. Magento does this. IE:

    Code:
    SomeTable(primarykey)
    SomeTableAttributesNumbers(sometableforeignkey, somenumbername, numbericalvalue)
    SomeTableAttributesVarchars(sometableforeignkey, somevarcharname, varcharvalue)


    Where does one draw a line between what data should be in SomeTable and what should be in a SomeTable attributes table? I'm sure an article has been written about this. Does anyone have a link?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think what you're looking at is called an entity-attribute-value or EAV structure

    lots of opinion out there on the web, most of it unfavourable

    even with multiple tables, one for each different datatype, it's still pants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm with r937 on this one, the line gets drawn at the point you consider creating an attribute table... That's almost always a bad idea from the start!

    THe whole concept is called EAV, for Entity-Attribute-Value. There are a few circumstances where EAV is appropriate and correct, but those circumstances are so rare that odds are very good that you'll never see one in your lifetime. I'm definitely past seeing 10,000 implementations for EAV and I will only conceed that 3 of them could be considered correct solutions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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