[RESOLVED] Database Relationship Problem - need advice

[Greyskull]

Hello,

An extract of my current database :
http://img150.imageshack.us/img150/1...agramexpb2.jpg

there are two different types of product that the database stores: packaged and fresh. This set-up is fine until i've realised that each store has a different price(PricePU) for each fresh products but has the same price(PricePU) for packaged stuff, making this set-up ineligible.

My initial thought was to simply transfer the PricePU attrribute to tblStoreStock, however, this could lead to inconsistencies for prices of packaged goods as well
My second idea is to create two different product tables (f_tblProduct & p_tblProduct) linked to f_tblStoreStock and p_tblStoreStock respectively. This set-up however could lead to the Primary key in each table (ProdCode) to be duplicated.

Are there any better solutions I cud do? if not whihc solution do u think is better?

Thanks in advance,

[sparbag]

Hello,
You have me slightly confused so perhaps I should request clarity:

I believe that each product should have its own ID value. If this value requires more than a single field - such as a product code and type then this should give you its primary key sufficient to meet the requirements of your design.

This would require the implementation of a "Candidate Key value" within the table.

The Distribution Centre entity will have a relationship with the store and will have a relationship with the product BUT the MAJOR relationship will be with the store.

My advice now would be to start working from the two types of product and to build a way to seperate them within the system through treating them as a specialisation of a product (a product can be a fresh product or a non fresh product. Two tables) OR using a candidate key value (such as fresh/Nonfresh) as a field within a table to identify the difference between them.

If any of this post has further confused you then my apologies. What you must not do is look at a product and use its price as a description of what it is, especially as this is the only part of the product that will change.

Kind regards

Steve

[brin351]

My questions to resolve is where does the store price variation enter the equation. If possible your new table/s relationship should reflect this logic.

do store prices vary but distribution prices remain the same to all stores
or
do distribution prices vary depending on store

for example it may be appropiate to have a productStorePrice table and the price NOT in the product table. So the packaged would all be the same anyway but the fresh can be varied.

[Greyskull]

Thanks for your replies, I'll clarify this first with the client as im now confused :S
I'll come back to you once I;ve clarified this.

[FunkyDexter]

Logically, you're price belongs on the product table for packaged goods and the storestock table for fresh. That means that Fresh and Packaged goods are now going to need to be handled differently. I can see a few options:-
1. Move the price to Store Stock and enforce the rule that it must be the same for packaged goods with an integrity constraint or trigger (probably the right thing to do but may tie you to the current DBMS)
2. Move the price to Store Stock and enforce the rule that it must be the same for packaged goods in your code (not as tight as the above solution but it allows for portability)
3. Create two sets of tables, you should really have a FreshStock and PackagedStock Table. To support that you should also have a StoreFreshStock and a StorePackagedStock table. You could then put the price field in the apropriate table out of each pair.

[Greyskull]

Right, i got it confirmed. The price for each fresh products differs from each store such as that an item i.e. cabbage (price per unit)

Store 1 - £0.79
Store 2 - £0.65
Store 3 - £0.99

Since cabbages sell the most where store 2 is.

To clarify,

The store sells two types of products: fresh and packaged.
Fresh goods are more seasonable in terms of consumer demand and consequently are ordered by the manager of the store based on his/her prediction of demand.
Pricing of packaged goods is done on a universal basis, i.e. the prices are fixed.
Prices for fresh goods are set by each store . (as mentioned above)

ProdCode is a 'candidate key' since each product has its unique barcode

To sparbag, i don't quite get what you meant by major relationship between the store and the distribution centre...sorz

Hope i made it clear this time.

[Greyskull]

1. Move the price to Store Stock and enforce the rule that it must be the same for packaged goods with an integrity constraint or trigger (probably the right thing to do but may tie you to the current DBMS)

That was my initial idea but I don't know how to add a constraint that will make sure that the packaged goods have got the same price for each store.

[szlamany]

If you have two tables for cost - one for fresh and one for packaged - you will cause yourself major headaches in every single query you every write.

I'm guessing the problem is that the cost of fresh produce varies for each store - so the "store id" needs to be part of the "key" of this table.

But the "packaged" goods are the same cost regardless of "store id".

I see two solutions...

Both required that "store id" be the second segment of a compound primary key on the price table (Product Id + Store Id).

One solution would be to copy the "packaged" price values to rows for each "store id". This would make the queries going forward extremely simple. Only issue would be that your user interface would need to make sure to update all rows for a product if it's a "packaged" type.

Soluion number two would be to have the "store id" value be "0" for packaged products.

So your JOIN to price would be

Left Join Price PR on PR.ProdId=XX.ProdId
       and (PR.StoreId=XX.StoreId or PR.StoreId=0)

btw - have you dealt with the issue of price changing over time??

[Greyskull]

Yes I've done some price updates before on a different database, but the price set for each store is the same just by using a simple Update statement. This problem gave me a bit of headache thinking about maintaining data integrity.

I can see how the first solution could help so are you suggesting that I move the Price and Type to StoreStock table or just the price?

[szlamany]

What is your backend database?

[Greyskull]

Im using MS SQL Server Management to build the database but I think they're planning to move this onto SQL Developer for Oracle at some point.

[szlamany]

Ok - my opinions...

PricePU does not go in tblProduct. tblProduct describes non-changing aspects of a product.

tblStoreStock already has a purpose - we don't need to put PricePU in that table.

PricePU should go into a tblPrice table.

It should have a compound primary key - made of two fields.

ProdCode - primary key segment 1
Store Id - primary key segment 2
PricePU - price

I would store a "packaged price" with a store id = 0 personally.

Now to update a price you would do

Update tblPrice set PricePU=123
     Where ProdCode='xyz' and (StoreId=999 or StoreId=0)

By using the OR in the UPDATE you make sure to update a price regardless of whether it's a single store price or a global price.

That same where clause is used on a SELECT to get a price. And variations of that are used on JOIN clauses to get a price for a sale for instance.

The only important thing for you to manage is that you never put a price into a table for a product with both a store id that isn't zero and a store id that is zero.

You can do that through tight USER INTERFACE rules or a trigger for protection against such an issue.

[Greyskull]

Thanks, I'll give this a try.

One question though, how would you make the storeId as zero? atm Ive got 10 stores, the storeID is something like KU01 to KU10. I could easily change this to an int so that ID can be from 1 to 10 but i don't know see how making it zero work if im not referring to any of the storeid's

[szlamany]

I thought store id was an INT - so I suggested zero.

If it's character data then make it "blank" - an empty string.

[Greyskull]

Woops, my bad. It's an int now

Happy holidays! Thanks again.