Relationship Complications

[timoz]

Hi all, 

I am attempting to analyse retail pricing of products in multiple locations.

I have the following tables:

Products

Set Price

Sites

Published

Groups

(My other tables, GenericProd and Products_Analysis are to assist with grouping similar products and calculating profitability)

Each Product can have multiple Prices. Each site is associated with just one group, but can be also advertise in a publication, which may be a different price (and therefore should override) the Group price. 

Can't get my head around how to relate my tables. My clumsy first attempt is attached. If the published price is less than the Group Price, for example, I can't work out if my relationships should be allowing for that, or if that is a calculation field issue.

My goal is to be able to view each product and, using a portal, see the current prices available for each Group AND each current publication. This will not only allow me to ensure no advertised price is higher than site prices, but also help me analyse the impact on site profit if a site chooses to move from one Group to another.

Any ideas? 

[comment]

Can't get my head around how to relate my tables

I don't think we can help you without understanding what type of real things your solution intends to track. Without this, Sites, Published and Groups are just words. I sort of gather that each one of a product's many prices is targeted at some venue. Why wouldn't all of these live in a single table?

[timoz]

I am comparing prescription drug prices. There can be multiple brands of the same drug. There are multiple sites selling the drugs and each site can have it's own  price. I need to be able to track changes in prices and advise on competitor's pricing as well as profitability impact based on any prices changes.

I rejected all prices in one table because I would be continually adding new columns each time a price changed.  

Products are the brand names of the drugs,

Generic is the molecule name

I am extracting the Strength and pack size from the Products and concatenating that with the Generic name to produce a Generic+Strength list. This will allow me to compare different brands of the same molecule/strength/pack size.

 

Set Price contains all recorded prices, one line for each price, so each Product can have many prices 

Published is the source/destination of the price- whether that is an advertised price in a catalogue, on a website, or just the price in that store.

Groups is my attempt to tie the disparate Published information into some standard.

My goal is to:

1. Compare the prices of a various brands of the same Generic

2. Track changes in prices over time 

3. Group sites that are using similar or identical pricing strategies

I will also (I hope) be able to enter sales quantities and calculate how much profit or loss would be made by moving from one group to another. 

Does that help?

[comment]

From what I managed to understand, it seems to me you should have:

GenericNames -< Products -< Prices >- Vendors >- Groups

In the Prices table, there should be an individual record for each distinct price, with presumably these fields:

• PriceID (auto-entered serial number)
• ProductID
• VendorID
• Price
• Units
• cPricePerUnit
• EffectiveDate

You might need to add more, if you intend to compare e.g. price per weight-unit of active ingredient.

I didn't understand this part:

I rejected all prices in one table because I would be continually adding new columns each time a price changed.  

As I said, each change of price would be recorded as a new record.

Or this part:

I will also (I hope) be able to enter sales quantities and calculate how much profit or loss would be made by moving from one group to another.