Normalization

  • hi,

    I have three tables TableA,TableB,TableC.

    TableA with Aid(primary Key), Aname.

    TableB with Bid(primary Key),Bname,Aid(Foregin Key from TableA).

    TableC with Cid(primary Key),Cname,Bid(Foregin Key from TableB),Aid

    Here TableA and TableB are realted and TableB and TableC are Related.

    I want to know whether it is against Normalization to store TableA Id in TableC With out relation?

    Plz Help

    regards

    veena

  • If there isn't a relation between TableC and TableA, why would you want to store Aid in TableC?

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • The purpose of a relational database is to have foreign keys where foreign keys exist. (There are exceptions,of course..in some transactional tables you may not do that-- although you'd store more than just the key there.)

    "Who then will explain the explanation? Who then will explain the explanation?" Lord Byron

  • hi,

    thank you for replying....

    For Some reason i want to Store TableA id in TableC....

    Plz Tell me Whether i can do this in a database where Normalization rules are applied?

    regards

    veena

  • I understand that, since you asked that in your first post. For some reason I like to know why you want this, because you might think there are limitations where there aren't.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • You can store tableA data in TableC without a relation, but without a relation you have no way of knowing that the data is valid. If TableA data gets deleted, it'll still be sitting there in TableC. Depending on what you're doing with this, you really could run into problems.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • tableA will be the parent table, TableB Child and TableC grandChild..

    so in this case if i want to Query records from tableC which is related to tableA, i have to inner join three tables. Sometimes if there is n number of levels so it will be easy if i store parent table ids in child tables..

    For this i want to store tableA id in TableC.

    regards

    Veena

  • With well indexed tables, a three table join is pretty painless. A ten table join is pretty painless too depending on the indexes. I sure wouldn't compromise the data integrity because of a few joins. But if you really want to go that route, then go all the way. Make the PK of TableB a compound PK incorporating the FK column from TableA. Then, when you refer down to TableC, it can have both columns, the one from TableA and TableB, and you only need a foreign key to TableB, but you'll be assured of data integrity.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You could make "c" a child of both "a" and "b" using FK constraints to "a" from both "b" and "c". I would always avoid using compound primary keys.

    As pointed out earlier you should not need to do this, it is not a best practice and unless you have a really strange situation the relation between "a" and "c" is artificial and can/should be resolved through "b".

  • Table B looks like a typical "mapping" table in a many-to-many relation ship. It must be populated manually since there's no way to accomplish the task automatically short of a triangular or full-square (Cartesian Product) join.

    --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)

  • veena (3/2/2009)


    hi,

    thank you for replying....

    For Some reason i want to Store TableA id in TableC....

    Plz Tell me Whether i can do this in a database where Normalization rules are applied?

    regards

    veena

    Well... That depends what normalization rules you want to apply! If you are only aiming for 1NF then I think you are fine (but aiming pretty low). I generally aim for 3NF or BCNF in which case the answer is NO. It cannot be done - you will be breaking the rules.... "Every non-key field must provide a fact about the key, the whole key, and nothing but the key (so help me Codd)".

    Bevan Keighley

  • Bevan keighley (3/3/2009)


    veena (3/2/2009)


    hi,

    thank you for replying....

    For Some reason i want to Store TableA id in TableC....

    Plz Tell me Whether i can do this in a database where Normalization rules are applied?

    regards

    veena

    Well... That depends what normalization rules you want to apply! If you are only aiming for 1NF then I think you are fine (but aiming pretty low). I generally aim for 3NF or BCNF in which case the answer is NO. It cannot be done - you will be breaking the rules.... "Every non-key field must provide a fact about the key, the whole key, and nothing but the key (so help me Codd)".

    Bevan Keighley

    Exactly, which is why, if you go with compound keys, it works.... Mind you, I'm not crazy about compound keys, but they have their place.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • A big disadvantage of redundant data is increased complexity of maintenance. Say you do add a "grandparent" foreign key A-ID to TableC. Then, if you ever change the A-ID in a TableB row to relate to a different TableA row, you'll also have to change the A-ID in all the TableC rows that are children of that TableB row.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply