Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer
    
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

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.

share|improve this answer
    
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. –  Emmad Kareem Jul 2 '12 at 17:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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