| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2003-08-01 : 00:09:20
|
| Statement:"The process of denormalisation results in a higher level of effort required for data maintenance."I'd be interested to hear what a few of you have to say in response.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-01 : 05:30:19
|
| Isn't Denormalization another word like "unremarried"?Sam |
 |
|
|
Argyle
Yak Posting Veteran
53 Posts |
Posted - 2003-08-01 : 06:05:36
|
| I would say that the statment is perfectly valid. If you denormalise and add redundant data more effort will be required to add, update or delete data./Argyle |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-01 : 07:21:06
|
| When you denormalize you take away SQL's ability to maintain data integrity through built in DRI constraints. Now you have to maintain data integrity with triggers and other DML that you must write to verify every INSERT/UPDATE/DELETE.Rarely is the trade-off beneficial in terms of overall system performance.Jay White{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 09:14:55
|
quote:
Originally posted by Page47Rarely is the trade-off beneficial in terms of overall system performance.
ummmm....Want a fast drill in for family trees?Brett8-)SELECT POST=NewId()EDIT: And what's with the "S"...Query Optimiser...DeNormalisaltion? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-01 : 09:33:54
|
quote:
EDIT: And what's with the "S"...Query Optimiser...DeNormalisaltion?
Ever hear of British-English? Similar to, but not the same as Aussie-English  Reminds me of the story about an Australian diplomat who visited the US, and remarked how different American English was from Australian English. He observed: "To you a bison is an animal. To us, it's something we wash our hands in"  Cheers mates!Owais |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-01 : 09:45:10
|
| Brett --I think by performance, Jay meant maintaining the data itself, not necessarily reporting. I think it's pretty well accepted that denormalization CAN simplify and speed up reporting -- especially for complex databases (i.e., Accounting systems).- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 10:04:34
|
| Then what does "trade off" mean..Was it an inference that the trade off for simplifing the structures, so instead of three there are 1 table?Then I would say that Jay should have made his point more strongly...forget perf boostIt is worse than that...there is no beneficial to that, and there are inherent problems...data quality, ectFunny you mention accounting...That's where I employee a drill in ledger by organisations...Brett8-)SELECT POST=NewId() |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-01 : 10:13:28
|
I think I must be getting senile .... I don't understand one single sentence in your post !What are you trying to say? denormalization is a GOOD thing and doesn't have any affect on data maintenance?I very confused .... - Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 10:27:11
|
Senile at 29? Come on...or did you forget to update your bio...Sorry for being murkyquote:
Rarely is the trade-off beneficial in terms of overall system performance
I was addressing this...what does that mean to you?Brett8-)SELECT POST=NewId() |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-01 : 10:39:38
|
| I think by performance, Jay meant maintaining the data itself, not necessarily reporting. I think it's pretty well accepted that denormalization CAN simplify and speed up reporting -- especially for complex databases (i.e., Accounting systems).- Jeff |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-01 : 12:58:06
|
http://www.dmreview.com/master.cfm?NavID=193&EdID=5251EDIT: Google morequote:
what does that mean to you?
To take the arguement to the physical implementation layer and water it down, here is an example.Say you have a database of employees and departments. An employee can be in one department. Location is an attribute of department.A normalized schema would have an employee table, with a foreign key referencing the primary key of the department table. The department table would contain the location. Querying the employee locations requires a join on employee to department on the department pk.A denormalized schema would have one table, employee. Department would be an attribute of the employee and location would be a transitive dependency (dependent on a non-key value...department). Querying employee locations does not require a join. Yippee.The arguement is basically this. Any benefit (in performance) of your employee locations query due to the denormalized schema is generally outweighted by the added dml required to maintain data integrity (a trigger or other dml to make sure that someone doesn't insert/update a location which is different from the the other department locations of the same department).Pascal explains it much better than I ... read his book.Jay White{0} |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-08-01 : 14:55:42
|
| Denormalization generally refer's to taking a database that is CURRENTLY normalized, and denormalizing it for performance reasons.Many people tend to think "I must make this fast, so I'll design it denormailzed from the start!" but I think that's not the right approch.If you haev normalized data, denormailzing it for performance is easy. Going back the other way isn't.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 15:14:59
|
WOW, that's an interesting snippetquote:
This definition of normalization is misleading, because denormalization tends to "unbundle" attributes -- the opposite of "grouping" (see Chapters 5 and 8 in my book).
I gotta get this book...Brett8-)SELECT POST=NewId() |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-03 : 20:18:30
|
| Thanks all, actually the reason I asked is because I have a customer (with a large and complex normalised schema) - he's convinced that all I have to do to make everything run superfast on a laptop is to denormalise it...problem was, after I'd been banging my head against a wall for an hour or so...I needed a sanity check and a stiff lagavulin (one seemed to work more quickly than the other).Oh, and Owais - nice to hear some cultural sensitivities out there!!!!(crikey, I just better go stick another prawn on that true-blue-fair-dinkum barbie maayeet!)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Denormalisation