Currently Being Moderated
Hi,
It's tricky to think about a data model without knowing the full requirements, but if you are just designing a POC for now then I think you can demonstrate a good working model of this system.
There are many ways to do this I'm sure... but here is my two cents...it's difficult to explain it on here in plain text but I'll give it a shot.
data model:
the product (contained by global. to many.)
--the product type (base, static)
--the product's cheapest price (inferred, possibly temporal)
--the product's cheapest store name (inferred, possibly temporal)
the store (contained by global. to-many.)
--the store name (base, static)
the price (contained by the store, to-many.)
--the price's product (base, static)
--the price's amount (base, static)
--the price's store name (inferred, static)
if you want to make the prices temporal otherwise leave out:
--the price's start date (base, static)
--the price's end date (base, static)
the quantity (contained by the store. to-many.)
-- the quantity's product (base, static)
-- the quantity's amount (base, static)
Finally, it might be convenient to have the following:
the order (contained by global. to-many.)
--the order number (base, static) etc...
the ordered item (contained by the order. to-many.)
--the ordered item quantity (base, static)
--the ordered item product type (base, static)
This should be a good base model and should be flexible enough to demo pretty much everything. It is especially powerful because it allows for prices to be temporal. Store prices go up and down all the time, and this data model could easily handle that. It could give you the cheapest price on a given order date...(if orders were backdated, or scheduled for a future date)...but it may be best to keep the POC simple for now...
Some other ideas based on your requirements...
There are many ways of doing this, but here is one idea:
Create an inferred relationship between the prices and the product (linked by product type) - "the product's prices"
You can work out the product's cheapest price by doing something like:
the product's cheapest price = InstanceMin(the product's prices, the price's amount)
Then
the product's cheapest store name = InstanceValueIf(the product's prices, the price's store name, the price's amount = the product's cheapest price)
using "the order" and "the ordered item" allows you to handle things like quantities of products, and totalling up the entire order etc.
you can also add extra layers around availability... so the cheapest price is only selected IF that store also has the product available.
The main difficulty I can predict with this is around the availability and a potential loop (i.e. the order is only approved if there are products available, and products available = availability minus the approved orders.)...that would need some careful design and consideration and beyond the scope of a forum post!
So, some ideas there.
It's well worth investing a LOT of thought and time into this sort of data model, and think about every possible requirement up-front, because a poorly designed OPA data model can very quickly become restrictive and a real problem. But a good, flexible model can make seemingly complex calculations very simple with a small number of rules.
Hope it helps.
Feel free to contact me further should you need more assistance.
Thanks
Ben