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

    Efficient for handingly dynamic fields?

    Hello all,

    This is my first time posting. I really want to learn more about efficient database design. I have an idea I'd like to run by you.

    I would like my database to have store data in a dynamic way. A typical case data DB looked like this:

    CaseID | Case# | FirstName | LastName
    1 | 0045F | Brad | Smith

    If you want to add, say, "MiddleName" field you'd need to add a new column to the DB which caused trouble for our code in the past. I have an idea to do it dynamically like this:

    FieldID | FieldName
    1 | FirstName
    2 | LastName
    3 | MiddleName

    CaseID | Case#
    1 | 0045F

    CaseDataID | CaseID | FieldID | Data
    1 | 1 | 1 | Brad
    1 | 1 | 2 | Smith
    1 | 1 | 3 | Lee

    The CaseData table would be incredibly long. I realize some provisions would need to be made for multiple data types. However, structurally, is this efficient? In other words, is it easier for a DB to read a FAT table with a ton of data but less rows, or a LONG table with a ton of data but more rows?

    Using this design we could VERY easily add new fields by adding to the Field Table. and anyone who has data for the newly added field would just get a new record in the CaseData table.

    Tell me what you think. Thanks!

  2. #2
    Join Date
    Dec 2007
    London, UK

    You don't need to do any such thing. The fact is that EVERY database design is dynamic.. or should be assumed to be so. The best way to deal with change is to have lean change control processes that are able to respond efficiently to changing business requirements. That means developers, DBAs, etc. It's about people and process, not technology.

    The wrong way to deal with change is to dismantle and obfuscate your data model (your "EAV" anti-pattern) on the grounds that your developers don't have time to maintain it. That's like admitting that your developers / database support team aren't upto the job.
    Last edited by dportas; 05-11-09 at 15:54.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    As DPortas pointed out, in at least 999 of 1000 cases EAV is a very tempting disaster in search of its next victim. There are a few places where VERY SMALL, unmanaged data stores need the flexibility of EAV such as when your application needs to replicate the hive structure of the Windows registry.

    I've never seen a case where a structured store (something that might be reported as rows and columns) ever needed to be stored using EAV, or anywhere that EAV worked nearly as well as normal tables for structured data.

    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