Data Design Patterns

Header-lines tables design pattern

Piotr Kononow - Dataedo Team Piotr Kononow 1st December, 2018


Many real life objects are comprised of list of similar objects. This is found in many documents we create: invoices, orders, todo lists etc. This can be modeled as header-lines pattern where you have a header table that holds attributes of the entire object, in case of an invoice this would be:

  • issued date,
  • customer or
  • number

and lines table that represents specific subitems. In case of an invoice this would be:

  • product,
  • quantity and
  • price

Design pattern

Data model consists of 2 tables linked with one-many relationship (one header, many lines).

Headers table

  • Has its id column
  • Holds attributes of the document (that appear once per document, in contrast to the ones appearing on each line)

Lines table

  • Has its own id column
  • Has not null foreign key to headers table
  • Holds line specific attributes (the ones that differ from line to line)

Example: Invoices

This is a very common example of header-lines pattern: invoices.

Data model

Data model looks something like this:

Sample data

Data dictionary

Other examples

Header-line design pattern occurs in many databases, especially in business applications (Inventory, MRP, ERP, SCM, etc) that deal with documents. Examples of such documents can be:

  • Purchase order - order items (products, services)
  • Quote - quote items (products, services)
  • Requisitions - requisition lines
Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.