| Author |
Topic |
|
mikedavid00
Starting Member
8 Posts |
 Posted - 2008-12-04 : 15:55:48
|
Hi!In business I've noticed that it's standard practice to have a header and detail table. OrderHeader Table would contain:Order Number Total Shipped Total Charge============ ============= ============25565 10 $101 OrderDetail Table would contain:Order Number PartNumber Total Peices Total Charge============ ========== ============ ============25565 WCO234 3 $3025565 01000342 3 $2025565 CBDFLK 3 $4025565 GBVVVDS 1 $11 My question is.....The same information in the header table can be generated from the detail table easily so I dont undestand whey there are header tables for..Is there a reason? It sure does over complicates joins, kill performance, ruin reporting, and data integrity.Can someone tell me what header and detail table design is done? Thanks! |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-12-04 : 16:39:07
|
| it has Data warehouse design strategy. Read about dual level granularity in data warehousing books. |
 |
|
|
mikedavid00
Starting Member
8 Posts |
Posted - 2008-12-04 : 20:02:22
|
quote:
Originally posted by hanbingl
it has Data warehouse design strategy. Read about dual level granularity in data warehousing books.
I did a Google search for "dual level granularity" and got 5 webpages in total. None looked relevant.The header table seems to be a hardcoded view or reporting tool rather than a truly functioning table.The problem arises when we need to do joins and have to rely on nested queries becuase the orderNumber relationships are all out of wack. The of course there's the *massive* performance hit of all this.I guess I just don't get it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mikedavid00
Starting Member
8 Posts |
Posted - 2008-12-04 : 20:45:24
|
quote:
Originally posted by tkizer
There shouldn't be a massive performance hit with regards to a detail/header approach. If you currently have this, then you've got to figure out why as it doesn't have to do with the detail/header design. Why are the orderNumber relationships out of whack? Don't you use foreign keys to protect data/referential integrity?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Things happen. The header table does not contain certain information that the detail table does and vice versa. But that's still not enough reason to have both tables. The joins of OrderNumber are a 1 to many relationship, even though they're all the same order number and all describing the same entity. That is illogical to me and sounds like another 'institutional practice' that the industry does for the 'sake of doing' 'because it's always been done this way'. If you have 5 million records in Detail, and 1 million records in Header, and then have to join on OrderNumber (which is usually not a primary key as id's are realistically not used), then you are depending on aliases and nested queeries to get correct SUM's. And can expect at least a 5 fold performance hit.Plus, these long queries are emberassing to the person like myself creating reports using reporting services. Impatiant exec's do not enjoy sitting around for 3 minutes to look at a query. Turst me. They are extremely impatient and want instant results and don't care to here the reasons. School taught us the ideal way to program. Then the real world kicks in and things are done their way. The real world uses old systems with FoxPro/Dbase, header tables, and do not use the 'id' columns (those are there for show). The real world db admins are people not hired through talent, but rather a peice of paper or luck.So before I ditch the Header table, I need to find 1 good reason why to use it and why everyone else does beside data warehousing. My data warehousing is called (dbo.Orders2007). |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-12-05 : 11:26:29
|
| Data granularity means levels of detail. Depending on the requirement, multiple level of details may be presented. Many data warehouse have at least dual level granularity. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-12-05 : 16:55:34
|
| Order headers should contain order dates, who made the order, order processing info, the sales person who took the order, and so on -- whatever is relevant to the order as a whole, and not to each detail item. Order Details should just contain the details, all of the items in the order broken out one by one. They are two completely different things, but related of course, and that is why a good normalized design puts them into two separate tables. it's like classes and students; you store the teacher who teaches the class once, not over and over for each student in the classic. basic normalization. With proper indexes and good clean SQL, it is no less efficient than stuffing everything into 1 big table with repeating, redundant data.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-12-05 : 17:13:04
|
| Mike, http://books.google.com/books?id=rnG3vjy7iPoC&printsec=frontcoverGo to chapter 2 Page 46, it tells everything about data warehouse dual levels granularity. You shouldn't be joining the Header and Detail tables to get the information you need, DETAIL table should be the source to answer all your questions. You are also correct, Header table is used for frequent report that serves its own purpose, do not expect anything else out of it. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-12-05 : 23:51:34
|
quote:
Originally posted by mikedavid00
School taught us the ideal way to program. Then the real world kicks in and things are done their way.
Well....I don't got none of that there fancy dba schoolin', but I reckin I gots nigh on fifteen yars o' callusses on my fingers what sez yer not typing on a full keyboard.quote:
Originally posted by mikedavid00
If you have 5 million records in Detail, and 1 million records in Header, and then have to join on OrderNumber (which is usually not a primary key as id's are realistically not used)
Now, I'm not quite shore whats you means by that, mister. If'n that thar OrderNumber AIN'T what you call a primary key, then under what my pappy would call a "proper" database design, she wouldn't be no fereign key t'either, and like as not you'd a be join'n on whuchacalls a serigit key. An if'n that thar OrderNumber DO be a primary key, then you can betcher wireless mouse she's all indexed up like a well kept card catalog.quote:
Originally posted by mikedavid00
then you are depending on aliases and nested queeries to get correct SUM's. And can expect at least a 5 fold performance hit.
Mister. This here's a family forum. Thar ain't no call fer using sech strong words as "aliases" and "nested queeries" round here. (An fer yer education, "queries" got only one "e" in her, at a time leastways.) I figure they ain't made the normalized two-table schema yet that I can't aggregate six ways to Sunday without havin' to haul a mess of aliases 'n nested queries out of the coal cellar.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
"..Header" table and "..Detail" table - why?