Database Structuring

[noneofyo]

Hi,

I work for an electronics reseller and I'm working on the following project:

Every week we make a list of our products with our price for that week and go 'shop' our competitors to make sure that we're the lowest price around. We do this once a week. Our list contains about 400 products.

We want a database that contains all this data (The product number, date, and our price and the prices from our competitors for that product on that date). Once we have this, I'm going to connect to the database via ODBC and have a php file that takes input from a sales rep on the sales floor (a text box and submit button) and grabs the data from this database and makes a line graph (using fusioncharts) : showing how we're the lowest price for this product and that we have been for the last however many months.

Whew! Ok, so my question is simply this: With that project in mind, how would you structure the database?

Would you make 1 entry be the product name and specific date, and have our price and our competitors price as fields in that entry? Or would you have 1 huge entry for each product that contains all the price shopping for that product (covering all dates and competitors)?

Would you have tiny entries one for each product on each date for each manufacturer? (Would result in thousands of entries)

Maybe none of these?

Hope this all makes sense.

Thanks so much,

- David

[David Jondreau]

One record for each date/product/seller combination. Never worry about having too many records in one table, worry about too many fields in one table.

I'd say you need at least the following tables:

Products

Manufacturers

Prices

Weeks (maybe)

Is the price searching automated or do you go and search the web each week for all the prices? I think you can integrate the Web Viewer into the solution with very satisfactory results.

[noneofyo]

Thanks so much for the response.

Actually, we have sales guys go into the stores and manually verify our competitors prices.

The way that we want the price shopping process to be will eventually be this:

We will print them out a report that has all the products for the retailer that they're going to. It will also have our price and that retailers last week price. So if it's the same as the week before (which it usually is), they'll just put a check mark. Or if it's different, they'll jot down the price.

We'll definitely be looking at the most effective (and mainly easy) way for these sales guy to enter in this data. But I figured the first step in this would be to get some sample data in a database structured the way we want it.

Thanks so much for your help.