Normalization

Question

Normalization

veena-1005926

SSC Rookie

Points: 31
More actions
March 2, 2009 at 5:26 am

#155349

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

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

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

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

rutgersdba SSC Enthusiast Points: 199 More actions · Answer 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

veena-1005926 SSC Rookie Points: 31 More actions · Answer 3

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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

veena-1005926 SSC Rookie Points: 31 More actions · Answer 6

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

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

mooredc54 Newbie Points: 3 More actions · Answer 8

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".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 10

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 11

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

john.arnott SSChampion Points: 11882 More actions · Answer 12

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.