Projektowanie prostego schematu dezagregacji prognozy popytu

Wykonuję proste zadanie projektowania bazy danych jako ćwiczenie szkoleniowe, w którym muszę wymyślić podstawowy projekt schematu dla następującego przypadku:

Mam hierarchię produktów nadrzędnych i podrzędnych (na przykład Surowiec> Produkcja w toku> Produkt końcowy).

• Zamówienia są składane na każdym poziomie.
• Liczba zamówień będzie widoczna w tygodniowych koszykach przez następne 6 miesięcy.
• Prognozę popytu można wykonać dla każdego poziomu produktu.
• Dzisiaj można sporządzić prognozę popytu na dowolny tydzień w ciągu najbliższych 6 miesięcy.
• Prognozę popytu przeprowadza się dla tygodniowych koszyków na następne 6 miesięcy.

Prognozę popytu zwykle wykonuje się na wyższym poziomie w hierarchii (poziom surowców lub produkcji w toku). Należy ją zdezagregować na niższy poziom (produkt końcowy).

Istnieją dwa sposoby podziału prognozy popytu z wyższego poziomu na niższy:

1. Użytkownik określa rozkład procentowy dla produktu końcowego. Powiedzmy, że istnieje prognoza 1000 dla Work In Progress ... a użytkownik mówi, że chcę 40% dla produktu końcowego 1 i 60% dla produktu końcowego 2 w segmencie 10 .. Następnie na 10 tydzień (od niedzieli do soboty) od teraz, wartość prognozy dla produktu końcowego 1 wynosiłby 400, a dla produktu końcowego 2 wynosiłby 600.
2. Użytkownik mówi, po prostu dokonaj dezagregacji według zamówień złożonych względem produktów końcowych w segmencie 5, a zamówienia w segmencie 5 dla produktu końcowego 1 i 2 wynoszą odpowiednio 200 i 800, a następnie wartość prognozy dla EP1 wyniesie ((200/1000) * 100)% a dla EP2 byłoby ((800/1000) * 100)% prognozy dla „Prace w toku”.

Prognoza będzie widoczna w tygodniowych przedziałach przez następne 6 miesięcy, a idealny format powinien być:

product name | bucket number | week start date | week end date | forecast value | created_on

Tabela PRODUCT_HIERARCHY może wyglądać następująco:

id  |   name                |   parent_id
__________________________________________
1   |   raw material        |   (null)
2   |   work in progress    |   1
3   |   end product 1       |   2
4   |   end product 2       |   2

Tabela ZAMÓWIENIA może wyglądać następująco:

id | prod_id | order_date | delivery_date | delivered_date

gdzie,

prod_idjest kluczem obcym, który odwołuje się iddo tabeli PRODUCT_HIERARCHY,

Jak przechowywać prognozę? Jaki byłby dobry podstawowy schemat dla takiego wymagania?

Mój pomysł, aby wybrać zamówienia na 26 tygodniowych koszyków to:

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);

Ale to da tygodniowe wiadra, zaczynając od dziś, niezależnie od dnia. Jak przekonwertować je na tygodniowe niedziele w soboty w Oracle?

Proszę o pomoc w zaprojektowaniu tej struktury bazy danych.

(będzie używał Oracle 11g)

Okej, więc oto model danych, który wymyśliłem.

PRODUCT - do przechowywania informacji o produkcie i utrzymania hierarchii rodzic-dziecko

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)                  

ZAMÓWIENIA - do przechowywania zamówień na produkty

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"

PROGNOZA - do przechowywania prognozowanej wartości produktów (przechowuj wartość dla wyższych poziomów, przechowuj wartość dla niższych poziomów po dezagregacji od rodzica)

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 - do zapamiętania, która metoda została użyta do dezagregacji wartości z wyższego poziomu na niższy poziom i ile procent został przeniesiony na niższy poziom

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 - wymiar daty, zawiera informacje o dacie rozpoczęcia (musi być sobota) i dacie zakończenia odpowiadającej tygodniowi, w którym przypada określona data

date_key    NUMBER  "Primary Key
Not Null"                   
full_date   DATE    Not Null                    
week_begin_date DATE    Not Null                    
week_end_date   DATE    Not Null

Jeśli chodzi o numer koszyka. Obliczam datę rozpoczęcia tygodnia (w moim przypadku w sobotę) za pomocą następującej funkcji

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;