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
There are no comments. Click here to write the first comment.