SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: EAV-schemas

  1. #1
    SitePoint Member
    Join Date
    May 2006
    Location
    Russia, Komsomolsk-on-Amur
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    EAV-schemas

    Hi there!
    Gentlemen, could you show the eav (entity-attribute-value) models implementation (database dumps/uml/any other human-readable presentations) and describe how and where you had work with them? some troubles and features it provides?

    (sorry if post in wrong section)
    Last edited by zerkms; Jun 11, 2009 at 07:28. Reason: typos

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    stay away from EAV, you're only asking for a world of hurt

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

  3. #3
    <?php while(!sleep()){code();} G.Schuster's Avatar
    Join Date
    Mar 2007
    Location
    Germany
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, in some situations WAV tables do make sense.
    I implemented them once for user details - there's a main "user" table containing all the generic data like username, email etc. and a "user_details" table that stores additional (and optional!) data with historic values.
    As the number of details per user varies (fields are added sometimes) and I needed historic data this was, at least in my opinion, the best way to handle it.

  4. #4
    SitePoint Member
    Join Date
    May 2006
    Location
    Russia, Komsomolsk-on-Amur
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanx for links, but r937, if not eav - then what to use for organize "universal" catalogue, such as catalogue of electronics: the graphics card properties doesn't match to the iPod ones?

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,638
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Personally I like the pattern I now dub the "Metadata with a Payload" pattern. Basically, let's take something like users:

    Code:
    CREATE TABLE Users
    Id INT IDENTITY NOT NULL,
    UserName NVARCHAR(255) NOT NULL
    Password BINARY(256) NOT NULL
    EmailAddress NVARCHAR(255) NOT NULL
    UserDataXmlVersion NVARCHAR(50) NOT NULL
    UserDataPayload XML NULL
    Then your user object (we are using objects in 2009, aren't we?) is responsible for taking that Xml payload and deserializing it into a usable object. The Xml version column gives you the data to handle changing the UserDataPayload xml model without breaking stuff. You don't end up with gads of extra tables for these values. This pattern plays very well with ORMs as well. And, depending on the database system, you can oftentimes index and query XML if the need arises and you need to dive into that user data.


Tags for this Thread

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
  •