What is the database pattern called where (for example) the current price of a product is stored in one table, and when someone makes a purchase the current price of the product is copied to a line item table, and is redundant until the price changes?


Not every approach to common problems has a name. Certainly at least not a commonly agreed name. The term "design patterns" applies just as well to common database designs as it does to OOP. I would say data modellers are a little less enamoured with the idea of design patterns than OOP programmers.

I'm not familiar with a commonly agreed name for the design approach that you're describing. I call it transactional logging.

As an aside, the price in the sale item table is not technically redundant, even if the price hasn't changed. The price in the product table is the current book price, the price in the sale item table is the actual price charged. There is no way of knowing for sure what price was actually charged from looking at book price. Even if you have a book price history table it might have been possible, depending on your system's rules, for the price to be overridden manually. The two data attributes are semantically different, even if they happen to be coincidentally equal for some period of time.


Typically you'd have an Invoice table that contains 1 or more sold line items (as a child of Invoice) and each line item's price (as well as possibly other details) would be looked up from a lookup table (that would act as a parent for the line item and would contain the price of each item). A line item in this case has many parents. The price table plays the role of a reference or lookup table.

  • But does this pattern have a name? I mean it doesn't even have to be the example that I gave, it just has to be a series of 3 tables that operate in the same manner.
    – leeand00
    Jul 2 '12 at 17:03
  • Maybe @RobPaller answers this better.
    – NoChance
    Jul 2 '12 at 17:53

Sounds like you are describing dimensional data modeling where you have a fact table supported by multiple dimension tables. Your Line Item table is your "fact" table and then you have a product and price "dimension" tables. Your price table could very well be a fact table or a slowly changing dimension table as well because there is a time element associated with a product's price changing over time.

  • P.S. They call these abstract sorts of concepts "Design Patterns" in Object Oriented Programming...what they call them in Database Design?
    – leeand00
    Jul 2 '12 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.