I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw Material > Work in Progress > End Product).
- Orders are placed at each level.
- Number of orders shall be viewable in weekly buckets for the next 6 months.
- Demand forecast can be done for each product level.
- Demand forecast for any week within next 6 months can be done today.
- Demand forecast is done for weekly buckets, for the next 6 months.
Demand Forecast is usually done at the higher level in hierarchy (Raw Material or Work in Progress level) It has to be disaggregated to a lower level (End Product).
There are 2 ways in which demand forecast can be disaggregated from a higher level to lower level:
- User specifies percentage distribution for end product. Say, there's a forecast of 1000 for Work In Progress.. and user says I want 40% for End Product 1 and 60% for End Product 2 in bucket 10.. Then for 10th week (Sunday to Saturday) from now, forecast value for End Product 1 would be 400 and, for End Product 2 would be 600.
- User says, just disaggregate according to orders placed against end products in Bucket 5, and orders in bucket 5 for End Product 1 and 2 are 200 and 800 respectively, then forecast value for EP1 would be ((200/1000) * 100)% and for EP2 would be ((800/1000) * 100)% of forecast for 'Work in Progress'.
Forecast shall be viewable in weekly buckets for the next 6 months and the ideal format should be:
product name | bucket number | week start date | week end date | forecast value | created_on
PRODUCT_HIERARCHY table could look like this:
id | name | parent_id __________________________________________ 1 | raw material | (null) 2 | work in progress | 1 3 | end product 1 | 2 4 | end product 2 | 2
ORDERS table might look like this:
id | prod_id | order_date | delivery_date | delivered_date
prod_id is foreign key that references
id of PRODUCT_HIERARCHY table,
How to store forecast? What would be a good basic schema for such a requirement?
My idea to select orders for 26 weekly buckets is:
SELECT COUNT(*) TOTAL_ORDERS, WIDTH_BUCKET( delivery_date, SYSDATE, ADD_MONTHS(sysdate, 6), TO_NUMBER( TO_CHAR(SYSDATE,'DD-MON-YYYY') - TO_CHAR(ADD_MONTHS(sysdate, 6),'DD-MON-YYYY') ) / 7 ) BUCKET_NO FROM orders_table WHERE delivery_date BETWEEN SYSDATE AND ADD_MONTHS(sysdate, 6);
But this will give weekly buckets starting from today irrespective of the day. How can I convert them to Sunday to Saturday weeks in Oracle?
Please help designing this database structure.
(will be using Oracle 11g)