Okay, so here's the data model I came up with.
PRODUCT -- to store product information and maintain parent-child hierarchy
id NUMBER "Primary Key Not Null"
level_code VARCHAR2 Not Null
name VARCHAR2 Not Null
description VARCHAR2
parent_id NUMBER Foreign Key references PRODUCT(id)
ORDERS -- to store orders for products
id NUMBER "Primary Key Not Null"
prod_id NUMBER "Foreign Key references PRODUCT(id) Not Null"
order_type VARCHAR2 "Not Null Default 'Default'"
order_qty NUMBER Not Null
order_date NUMBER Foreign Key references DATE_INFO(date_key)
delivery_date NUMBER "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"
FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)
id NUMBER "Primary Key Not Null"
product_id NUMBER "Foreign Key references PRODUCT(id) Not Null"
forecast_value NUMBER Not Null
week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null"
DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level
id NUMBER "Primary Key Not Null"
parent_product_id NUMBER "Foreign Key id references PRODUCT(id) Not Null"
child_product_id NUMBER "Foreign Key id references PRODUCT(id) Not Null"
method VARCHAR2 Not Null
from_week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution NUMBER Not Null
DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls
date_key NUMBER "Primary Key
Not Null"
full_date DATE Not Null
week_begin_date DATE Not Null
week_end_date DATE Not Null
As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function
CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
RETURN DATE
IS
week_start_date DATE;
BEGIN
SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
INTO week_start_date FROM dual;
RETURN week_start_date;
END;
As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though.
In addition to web transactions, many businesses support sales through other channels, e.g.:
- Over the phone
- Sales agents "on the road"
- At a physical location (e.g. shop, office)
In these cases the order may be entered into the system at some time after the transaction took place. In these circumstances it can be difficult to impossible to correctly identify which historical price record should be used - storing the unit price directly on the order is the only feasible option.
Multiple channels often bring another challenge - different prices for the same product. Surcharges for phone orders are common - and some customers may negotiate themselves a discount. You may be able to represent all possible prices for all channels in your product schema, but incorporating this into your order tables can become (very) complex.
Anywhere that negotiation is allowed it becomes very difficult to link price history to the order price agreed (unlesss agents have very narrow negotiation limits). You need to store the price on the order itself.
Even if you only support web transactions and having a relatively simple pricing structure, there's still an interesting problem to overcome - how should price increases be handled for in flight transactions? Does the business insist that the customer must pay increases or do they honour the original price (when the product was added to the basket)? If it's the latter it the technical implementation is complicated - you need to find a way to ensure you're maintaining the price version in the session correctly.
Finally, many businesses are starting to use highly dynamic pricing. There may not be one fixed price for a given product - it is always calculated at runtime based on factors such as time of day, demand for the product and so on. In these cases the price may not be stored against the product in the first place!
Best Answer
Referential integrity is one, and is an attribute of the table data, but it is not an attribute of the table's schema. A table's schema shouldn't be changed in a cascading style, because it can break existing working code. A table's column is not used only in foreign keys, triggers, etc. It's used in scripts, maintenance plans, jobs, applications, dlls, even web pages etc. If you change an object's name you can create a mess, that's why it shouldn't be done automatically. This is something you want to control.
This is where refactoring tools can enter the scene. Eg for MS SQL Server there's Visual Studio db projects that can help here, but not by much. There may be tools that aid in refactoring the db objects and fix anything automatically, but they are not part of any standard db package.