Sql – Understanding Header and Detail tables

database-designdatabase-normalizationsql

I have seen this in various DB but I will use the most recent example.
In the AdventurWorks2012 DB, there are

  • PurchaseOrderHeader
  • PurchaseOrderDetail

and

  • SalesOrderHeader
  • SalesOrderDetail

I am trying to understand the concept of why you would have this setup when you can put all that information in one table instead of two. Sorry for my lack of knowledge on this type of setup. I want to make sure when I create new tables, I am wondering with this type of design, how this would work similar to what I may incorporate to capture Data Entry into a table using Header and Detail and the reason why.

e.g.

  1. Current Date
  2. Current Month
  3. Current Fiscal Year
  4. Company ID
  5. Revision Number
  6. Service Product 1 Amount
  7. Service Product 2 Amount
  8. Service Product 3 Amount
  9. Service Product 4 Amount
  10. Service Product 5 Amount
  11. Total Amount (Sum Items 6-10 above)
  12. Certification Date
  13. Certification Officer
  14. Submission Date

Hope my question is clear.

======================

Edited for detailed example of using Header/Detail setup via my response to @Szymon

In my above example, to layout it out as a Header/Detail setup. When a record is submitted, it generates an ID So my Header Table would look like:

Header Table

1, '11/13/13',11,2013,'000001',0,10.00,'11/12/13','President','Chuck','11/12/13'

then in my Detail Table it would generate an ID also but use the previous Record as a FK and it would look like this… 1 (New ID), 1 (FK of Header Table previously), 2 (As Service Product 1), 2 (As Service Product 2), 2 (As Service Product 3), 2 (As Service Product 4), 2 (As Service Product 5)

Detail Table

1,1,2.00,2.00,2.00,2.00,2.00

==================================================

Revised Again: To provide a better follow up example.

WKS_Header Table: (PK is WKS_Header_ID)

WKS_Header_ID [int] IDENTITY(1,1) NOT NULL,
Company_ID [varchar](6) NOT NULL,
Current_Date [DateTime] NOT NULL, 
Current_Month [int] NOT NULL, 
Current_Fiscal_Year [int] NOT NULL,
Revision_Number [int] NOT NULL,
Worksheet_ID [varchar] (13) NOT NULL,
Total_Amt [money] NOT NULL,
Certification_Date [DateTime] NOT NULL,
Certification_Officer [varchar] (50) NOT NULL,
Submission_Date [DateTime] NOT NULL

Sample Records of WKS_Header Table:

1,'000001','11/5/13',11,2013,0,'0000001111300',20.00,'11/1/13','Chuck','11/2/13'
2,'000001','11/7/13',11,2013,1,'0000001111301',10.00,'11/4/13','Chuck','11/5/13'
3,'000500','11/10/13',11,2013,0,'0005001111300',50.00,'11/5/13','Bob','11/7/13'

WKS_LineItems Table: (PK is WKS_LineItems_ID)

WKS_LineItems_ID [int] IDENTITY(1,1) NOT NULL,
LineItem_Description [varchar] (50) NOT NULL,
Create_User_ID [varchar] (50) NOT NULL,
Create_Date [datetime] NOT NULL,
Modify_User_ID [varchar] (50) NOT NULL,
Modify_Date [datetime] NOT NULL

Sample of WKS_LineItems Table

1,'Service Product Widget A Amount','Admin','10/1/13',Null,Null
2,'Service Product Widget B Amount','Admin','10/1/13',Null,Null
3,'Service Product Widget C Amount','Admin','10/1/13',Null,Null
4,'Service Product Widget D Amount','Admin','10/1/13',Null,Null
5,'Service Product Widget E Amount','Admin','10/1/13',Null,Null
6,'Final Total Widgets Amount','Admin','10/1/13',Null,Null

WKS_Details Table: (PK is WKS_Details_ID, FK is WKS_Header_ID from WKS_Header Table)

WKS_Details_ID IDENTITY(1,1) NOT NULL,
WKS_Header_ID [int] NOT NULL,
WKS_LineItems_ID [int] NOT NULL,
WKS_Amount [decimal] (18,2) NOT NULL,
Create_User_ID [varchar] (50) NOT NULL,
Create_Date [datetime] NOT NULL

Sample Of WKS_Details Table

1,1,1,4.00,'Chuck','11/5/13'
2,1,2,0.00,'Chuck','11/5/13'
3,1,3,0.00,'Chuck','11/5/13'
4,1,4,5.00,'Chuck','11/5/13'
5,1,5,11.00,'Chuck','11/5/13'
6,1,6,20.00,'Chuck','11/5/13'
7,2,1,0.00,'Chuck','11/7/13'
8,2,2,0.00,'Chuck','11/7/13'
9,2,3,0.00,'Chuck','11/7/13'
10,2,4,0.00,'Chuck','11/7/13'
11,2,5,0.00,'Chuck','11/7/13'
12,2,6,10.00,'Chuck','11/7/13'
13,3,1,10.00,'Bob','11/10/13'
14,3,2,10.00,'Bob','11/10/13'
15,3,3,10.00,'Bob','11/10/13'
16,3,4,10.00,'Bob','11/10/13'
17,3,5,10.00,'Bob','11/10/13'
18,3,6,50.00,'Bob','11/10/13'

Scenario:
Input information from a Form. It creates 3 records in the WKS_Header Table with associated detail records into WKS_Details table.

Record ID 1 is an original submission, then that user needs to revise the numbers. That user inputs another record. That is Record ID 2, a revised submission to supersede Record ID 1.
Record ID 3 is an original submission.

Do I have this normalized correctly?

Best Answer

This type of relationship is called one-to-many relationship. It is used when there is one parent record and multiple child records. You use that kind of relationship to normalise data.

In case of a purchase order, you have one header which describes the order, e.g. order number and date. There's only one set of this information for an order.

Then there's multiple line items, each with its own item name, quantity and price. There's many items for each table.

If you were to create only one table, you would have to repeat the information from the invoice header for each item. You would have the same order date and number repeated over as many rows as you have items.

This is bad for a few reasons, among them:

  • You have redundant data that takes unnecessary space and are hard to maintain (e.g you have to update multiple records if you want to update one information in the header).
  • Querying the table is more complex, e.g. when you want to display a list of order headers, you would have to use DISTINCT.
  • The structure is not standard (not normalised) and difficult to read for other people who expect a standard approach to database design.
Related Topic