Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2010-07-12 : 23:51:37
|
Everyone now-a-days stresses how important normalizing your data is. It makes sense, but here's my questionParentTable1 (ParentID PRimarykey,ParentDesc)ChildTable1 (ChildID,ParentID Foreignkey,ParentDesc,ChildDesc)So how do you keep the ParentDesc consistant with the ParentTable?? The foreignKey can have a cascade update, but that doesn't help the parentdesc. What's the best way to keep data consistant in a normalized database?Thanks!!
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddramireddy
Yak Posting Veteran
81 Posts |
Posted - 2010-07-13 : 02:12:58
|
| "Don't redundant or duplicate your data..."This principle will alone ensure 80% of normalized tables...Suppose, if u keep ParentDesc column in child table, what if in future, that parent description changes??? will u again change in child table??? Thats the problem with redundant data... always keep your data at only one place... if it changes in future, u no need to change at multiple places...u should keep parentDesc in Parent Table only... and u need to relate that record ID with the child table... thats all... |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2010-07-13 : 11:31:06
|
The theory behind this is it's benefitial on db performance if I do not need to join a parent table if I store the information I currently use in the child.This Increases performance. Is this just a bad practice? I ideally would like to have a cascade update on a column that does not have a primary key.Anyway to achieve?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-13 : 12:22:28
|
| May be splitting-hairs, but I'm not sure it does increase performance.You will have fewer child records per-page (because of the extra, redundant, information), thus you may have more page-reads to satisfy a particular query on the Child Table; the rows will take up more space in memory-cache, so will will cause other data to be dumped from cache sooner [than they would have been if the redundant data was not in the cached-row]For a Data Warehouse that may acceptable, or even desirable (where it is known that the table is scanned in a particular way), but I doubt it is beneficial in OLTPAnd you only need one instance when the Parent and Child values become different, and you have to spend time working out which one is right, for any benefit to be wiped out! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-13 : 13:12:49
|
| Denormalizing data for performance reasons is an old practice before 2005 days. SQL Server has come a long way in regards to performance, and I just don't see the need to denormalize anymore. Properly normalize your database, have good indexes, have efficient queries, and good maintenance jobs (update stats for sure). With this you'll see you've got great performance without ever needing to denormalize. We've got a 1TB database that has a performance requirement of 99% of queries completing in under 300 milliseconds. No denormalization needed!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Noramlize data how to keep consitancy?