Database design question

Hi there

I'm in the process of creating an order database that contains tables for products and an order table that contains information for orders. Should the order table have a xref to the products table to show what products are associated or should there be a separate table to show what products are associated for the order (ie a orderProduct table) that would also contain pricing. The reason for my question is for tracking order history. If the price of a product changes and the order table is associated with the product id then old orders will have the new pricing which is incorrect. Would it be better to when an order is place copy the values of price and the product info to an other table?

Thanks

Good thinking. Separate table. OrderProducts. OrderID FK, ProductID FK, OrderPrice. OrderPrice is the price at the time of the order.

Incidentally, can anyone delete a product if your customer discontinues it? If so, you might want to think about a YesNo/bit column to flag a discontinued product for suppression rather than wiping it from the database.

|||

I would recommend that you do that, for exactly the reason you state. I have worked with MS Commerce Server 2000 and it had a similar database design. Here is a simplified example from my vague memory:

Order
OrderID (primary key)
OrderNumber (human readable order number)
CustomerID (foreign key to customer)
Date
SubTotal
Tax
Shipping
GrandTotal

Product
ProductID (primary key)
Active
SKU
Name
Description
Price

OrderItem
SKU
Name
Price
Quantity
Total

The idea is that any field that could change throughout the lifetime of a product should be stored with the order in order to record exactly what they ordered. You may even think about moving the Price for Products out into a ProductPrice table like the following.

Product
ProductID
SKU
Name
Description

ProductPrice
ProductID (foreign key to product)
EffectiveDate (date this price becomes effective)
Price

This design allows you to change prices ahead of time and specify when they become effective. To get the current price you would select the ProductPrice with the highest EffectiveDate that is greater than or equal to today's date. This may be totally unnecessary though, it's just an idea.

Mark

|||thanks for the replies guys that does help clarify things. I'm currently having the same type of issues with a room booking database design. I want to have tenants book certain rooms for specified time intervals. The tenants can book rooms (rooms are stored in a table) and select things like room setup (configurations are stored in a table). All the tables have an active flag on then. I also have a bookings table but unsure as to how to store the rooms and configurations that are selected. Should i be using ids in the booking table? What happens if the rooms become inactive down the road and the client was to view past bookings? Do i leave out the check on the active flags and show the rooms anyway? I believe that i need the room ids in order to validate new bookings against what is already booked. If the admins want to edit bookings and say a room or configuration that was previously active is now inactive would i force the admin to select a new configuration? tables such as room and room configuration do not have an interface to change values or inactivate them, i'm just wondering for the future if they say they want to add a room and remove a room then past bookings will get affected.