product price comparison

[midnite]

Hello, I am new member and would appreciate your opinion. I am trying to build a new database of product prices. I have over thirty shops with products, and want to compare prices. Each product may be in several package sizes not necessarily the same in every shop. For example I may have a chocolate in three different package sizes in shop1, four different package sizes in shop2 e.t.c.

I want to be able to group each product, gathering all packages from all shops and then compare prices, by converting the other package sizes to the ones that I have in my shop, whenever they differ. Then, I would like to modify the price of product in my shop, to be the average of the three lowest prices that I found in other shops. My question is whether I should use one table for all shops, or individuals for each shop and if the latter, how should I define relationships? Thank you very much

[comment]

I'd say use three tables: one for the products you intend to track, one for the shops, and one for the prices - related as:

Products::ProductID = Prices::ProductID

Shops::ShopID = Prices::ShopID

[midnite]

Thank you very much, but I still do not understand whether I should have a table for my products and a separate for all other products in all other shops, or a unique for all products.

Thank you once again

[comment]

There is one table for all products being tracked, and one table for all their prices in the competing shops. For example:

Products

ProductID      Product        Units

1              Chocolate      150

2              Widget         50

...

Shops:

ShopID         Shop

1              Cheap Joe

2              Moe's Trap

...

Prices:

PriceID        Date           ShopID         ProductID      Price          Units          cPricePerUnit

1              1/1/2010       1              1              25             250            0.1

2              1/1/2010       1              2              50             100            0.5

3              1/1/2010       2              1              50             200            4

4              1/1/2010       2              2              20             50             2.5

...

[midnite]

I am eternally grateful, but there is one problem product list is not the same in all shops, that means that productID in third table is easily entered.

Thank you very much, I think that I am taking advantage of your generosity

[midnite]

I am eternally grateful, but there is one problem product list is not the same in all shops, that means that productID in third table is easily entered.

Thank you very much, I think that I am taking advantage of your generosity

[midnite]

I am eternally grateful, but there is one problem product list is not the same in all shops, that means that productID in third table is not so easily entered.

Thank you very much, I think that I am taking advantage of your generosity

Edited March 22, 2010 by Guest

[comment]

product list is not the same in all shops

It doesn't have to be. For any given date (or period) you can have 35 prices of Product A, 22 prices of Product B, and so on. As long as each price includes some unique identifier of the product it refers to - otherwise it won't be possible to compare them. The same applies to the shop, though I suppose this is less critical.

[midnite]

as I said before, I am eternally grateful.

I have to figure out how I will group prices before adding them at the prices table because my primary data are an excel file for each shop, having as rows products and columns package, brand company, price e.t.c

Thank you once again