Let's say a supermarket has one table with statistical revenue data by business day. In a second table they want to try out different variations of these data, f.e. less bakery revenue at 1st January, one more public holiday or maybe opening on sundays.
I do not want to duplicate the whole revenue data of a year or more for just a few variations. Additionally it should be easily possible to show the diff between the base table and one alternative scenario.
To make it a little more clear, I created a demo case at SQLFiddle. SUPERMARKET_DAYS is the table with the base revenue data. ALTERNATIVES are the variations/alternatives or scenarios (however you want to name it) and ALTERNATIVE_DAYS is a table with a similar structure as the first one and holds the concrete changes.
SUPERMARKET_DAYS
(
ID,
DAY,
NUM_CUSTOMERS,
NUM_VISITORS,
REVENUE_BAKERY,
REVENUE_BEVERAGE,
REVENUE_CANNED,
REVENUE_DAIRY,
REVENUE_DRY,
REVENUE_FROZEN,
REVENUE_MEAT,
REVENUE_CLEAN,
REVENUE_PERSONAL_CARE,
REVENUE_PAPER,
REVENUE_OTHER
)
ALTERNATIVES
(
ID,
TITLE
)
ALTERNATIVE_DAYS
(
ID,
DAY,
NUM_CUSTOMERS,
NUM_VISITORS,
REVENUE_BAKERY,
REVENUE_BEVERAGE,
REVENUE_CANNED,
REVENUE_DAIRY,
REVENUE_DRY,
REVENUE_FROZEN,
REVENUE_MEAT,
REVENUE_CLEAN,
REVENUE_PERSONAL_CARE,
REVENUE_PAPER,
REVENUE_OTHER
)
I do not even know, if this schema design makes any sense but it illustrates the problem.
- How would you store this kind of data?
- I thought about something like a versioning table but this does not really solve my problem.
- I could merge both tables inside of my java code.
- Is there something like the
MERGEfunctionality for views?
I would really be grateful for every kind of idea or the correct keyword for my own investigations. This problem should already exist but I didn't find much to solve it, so I maybe did not search with the correct terms.
Thank you everybody!
