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.


Have you looked into any 3rd party carts. VPASP has the code already done fully open source, and it's cheap

I know 3rd party systems exist but i'd rather learn how to do it myself. I reckon I'd already be able to do it but I'm curious as to the correct way of doing things rather than what I've self taught myself

The database design is based on how you are going to pull the variables on the site, for start if you want to do it yourself download I would download vpasp's free demo that comes with the scripts to create a database in access mysql and ms sql see how they did it and build yours off of theirs. There is no right or wrong way depends on how you want to organize the data