Hi,

I'm looking to set up an ecommerce site for a client with around 1800 products in it. There will also be around 100 customers that receive different discounts on each of these products. unfortuanately this is not calculated as a percentage but rather each individual customer pays a different price for a pack of meat etc.

I'm really just looking for some advice on how to design the database.

I was thinking of making the following tables.

A product table containing the product ID, title description etc.

A price table with the product ID, customer Id, and then the price.

And also a customer table containing the customer Id, company name etc.

Is this a good way of doing it or can someone enlighten me as to a better way to do it.

Cheers

Hi.

That sounds about right.
If each product needs a different price for each customer, then a N:M relationship, somewhat like you described, is usually the best way.

The only problem there is how you would store a "base price" for each product, if you need such a value.
Personally, I would be tempted to add a row for each product in the price table, where the customer ID would be null, and use that to store a base price for customers that don't actually have a price listed.

satan, get back, and take your evil temptations with you!!

you couldn't do that anyway, since the PK of the table would be a composite key of { product ID, customer Id }, and no part of a PK can be NULL

you do use PKs, right?

the best solution is to add a column called base_price to the products table

O no, he's on to me!

A good point. Not that this couldn't be done tho. You could use a auto_increment PK and set the two ID's up as a unique key.
Or, if you are about to get fired and you hate the guy who is going to be replacing you... you could simply leave them to be duplicated.

But all in all, you are probably right. Storing it in the product table would probably be best.