Home Forums Database Design Design Ideas and Questions Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type RE: Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type

  • iBar (7/31/2012)


    Hi L' Eomot Inversé,

    One thing you might have missed OR may be i could not understand in your reply is the flexibility for future enhancement.

    For example:

    In first approach, if one need to add a new column in future DOT which was missed in initial design then that needs lots of changes in DB and application as well, atleast upto some extent.

    However, in Second approach, having an "Employee key dates" column with "type of date" column could give lots of flexibility. so if DOT was missed in initial design then by adding the data in type table will allow you to save a new type of information without any structure change? This will also give more control on data growth.

    Jeff, I could not understand your point about "How about durability and history?" Do you mean what i have tried to explain in second approach?

    SO which one do you recommend, more specially from performance point of view and flexibility for future enhancement? Please tell any any pros and cons.

    Thanks and Regards.

    The concern with you A2 approach (the EAV-style approach), is that it tends to quickly degenerate into an unmanageable scale. As in everything ends up being modeled into the Entity-Attribute-value tables, so querying for complex criteria becomes a perofrmance and scalability disaster. Try taking on a query like "find me all long term (5-years or more) customers whose name starts with 'A', live in Delaware and have had 5 or more orders over 500$ in the last three months" in one of those pure EAV scenarios, and you quickly discover just how big of a mess you've created for yourself. Or - try to enforce relational integrity (or any form of data constraint for that matter) on any attribute in an EAV model.

    The best approach IMO is a blended approach: build out using a traditional relational model whatever parts of your model you KNOW, and capture the oops'es using EAV as an interim step, WHILE you build out the changes to your longer-term stable model. A1 tends to leverage the strengths of the relational model, so should be the long-term answer for most things; A2 might have some flexibility, but loses a lot of features inherent to relational approaches and tends to invite performance and reliability issues.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?