How many tables should I use?

[Guest]

I am designing a DB to list my customers prices. I looked at northwind and it
uses three Products/order details/orders.

I want to show data in a form using customers, then a subform using the
products - if I alter the price in the subform of one of the products for
that customer will this alter that same price for all my customers? or do I
need a third table like order details in northwind?

 

[Tim Ferguson]

I want to show data in a form using customers, then a subform using
the products - if I alter the price in the subform of one of the
products for that customer will this alter that same price for all my
customers? or do I need a third table like order details in northwind?

There are (at least) four different things that need to be modelled:

Customers, who each have a name and an address and a credit card(*);

Orders, which each have a date and a signatory and a delivery date;

Orderlines, which each have a productID and a quantity and
a priceCharged;

Products, which each have a description and a partNumber etc etc.

The price paid _might_ be an attribute of the product, but only if you
know that the product.price will never change. I guess that it unlikely,
and that you will need to know the price that the customer was charged at
the time of the order. Therefore it belongs in the OrderLines table.


(*) of course, if any of those eaches are not true, then you probably
need (at least) another table to model it...


Hope that helps


Tim F

 

[Guest]

If a product's price varies by customer, then the price of a product does not
appear in the Product table. You must have a Customer-Product table that has
customerID, ProductID and price. This table will be used to look up a price
for a product when filling out an order for a customer.

 

[Public]

Northwind copies the price from the product table and places it in the Order
Detail table (I think). This is correct since price changes over time. A
more complex method is to keep historical price information in the product
table but that would require complex queries to obtain the price that was in
effect at the time of the order plus that method doesn't provide for easy
override of pricess. So, copying the suggested price into the order detail
saves the price at the time of order plus gives you the flexibility to
override the price as the order is placed and that unique price is only for
the order detail which you edited.