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

  • July 30, 2012 at 10:16 pm

    #1519514

    iBar (7/30/2012)

    Hi,

    Which one is a better design strategy with respect to flexibility, performance and maintainability?

    A1: Defining Nullable columns in one table

    OR

    A2: Define character keys to identify attributes an entity/table and only populate the keys when data is available?

    For example:

    In A1 design table is

    Employee (Emp)

    EmpId, name, pay, department, dob, doj, dot

    In A2 design

    Employee (emp)

    id, name, pay

    Employee detail (edt)

    EmpId, EdtId, Edt_Data_type, data_value

    A1 is good for easy maintainance and might give better performance for reporting and searching? Might be more good with sparse columns?

    A2 is giving more flexibility in terms of future enhancements and easy adoptability of any enhancement. Simply add a new EdtID and no structural changes required as such. So lesser future development?

    What do you suggest?

    Thanks.

    How about durability and history? I wouldn't put pay rate in the same table as the employee information, for example. I would, however, have a Type 2 Slowing Changing Dimension table for such a thing. Same for phone numbers, addresses, department history, and a couple of other things.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)