Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    one table per attribute?

    I posted a thread earlier about using EAV versus one table per attribute because I need to allow users to define their own attributes (I'm basically stuck at having to allow this). I'm sufficiently worried about implementing EAV given its drawbacks, so the only sensible solution I can think of is to just create a table for each user-specified attribute, which would tuples of (entity_id, attribute_value). If I'm not mistaken that's basically a DKNF table. I would obviously need to have a lookup table for getting the table names for attributes in order to perform desired join queries. I was wondering if anyone has an opinion good/bad about this kind of database plan for allowing user-defined attributes. thanks.

  2. #2
    Join Date
    Dec 2007
    London, UK
    Having at most one non-key attribute per table is called Sixth Normal Form (Date, Darwen, Lorentzos). It is a potentially useful way to deal with a regularly changing schema or one where all attributes are independent of each other. In practice it isn't usually necessary to go that far though because at least some attributes normally are associated, ie: they go together so that you would never have one without the other.

  3. #3
    Join Date
    Mar 2009
    Sydney, Australia
    EAV is definitely bad news, unless you like masses of code that do not work. Just because the users are allowed to define their own attributes does not mean the entire data model has to be based on that: you can have a proper Normalised structure and use either a simple 1:n table or 6NF, just where they need to add attributes. You don't want the whole database to be broken, just the bit that they keep changing.

    I do not think that is DKNF. Where I have seen formal DKNFs, they have had no RI at all, so the Domain-Key requirement didn't have DK to be based on DK. They said they had to remove the RI to "make it work". IMHO DKNF is too informal and non-specif, anyone who is trying to comly with some xNF, who does not make it, trumpets that they have DKNF; because it is non-specific, most people find it hard to prove that the declaration is false.

    Stick with the "real" normal forms, at least 3NF and BCNF for the main database.
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

Posting Permissions

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