Hi there!

I am creating a database to hold all the data for a customer portal for a business.

The portal will hold all information about the orders that customers place and the status of the orders.

The business offer different prices to each customer for the products and this has created a bit of a problem in terms of my design for the database.

My current ideas are as follows:

Orders Table (details of each order including the total price for the order)
OrderItems Table (details of what items are included in the order)
Products Table (containing all the details of each product they sell excluding the price of each item)

I now need somewhere to store each customer's price list and still have a link to the Products table.

Does anyone have any thoughts as to how I may achieve this?

Cheers!

just like OrderItems is a many-to-many table between orders and products, you need CustomerItems to be the many-to-many table between customers and products

then the product_id in the CustomerItems table is your link back to the products table

Sorry to be a pain here but could you explain this a bit more to me?

Could you literally explain what columns I should make to store the prices?

Thanks for you help!

sure, no problem

CREATE TABLE CustomerPrices
( cust_id INTEGER NOT NULL
, FOREIGN KEY (cust_id) REFERENCES Customers (id)
, product_id INTEGER NOT NULL
, FOREIGN KEY (product_id) REFERENCES Products (id)
, PRIMARY KEY (cust_id,product_id)
, price DECIMAL(9,2) NOT NULL
);

those are the columns you will need

Ok cheers for that.

Now my second question! Sorry!

Say I now want to enter the price for Product1 for Customer1 into that table.

How do I format this?

Thanks for this!

Brilliant!

Works great, thanks for your help!