Database design issue

I have a table Orders. Each order can have the same product multiple times. Quantity is not enough as each product may have different preferences! So i am wondering what is better to do...

PK-id order id product id ? Primary key is PK-id

or

Orders Table

(Order-id ,orderline) as PK..

Then another table

Orderlines

(Order-id, orderline, productid) as PK

And then

OrderLinesPreferences

(Order-id ,orderline, productid, preferenceid )as PK ?

I wonder whether this approach is ok .Also i dont know what i should i do about indexing in order not to get slow when records are many. I am using postgresql..

For example in table OrderLinesPreferences a where 开发者_运维百科order-id=1 and productid=10; would be fast or would do a full table lookup ?

Based on a comment made by @OP to an answer by dnuttle, I would say the situation calls for items and sub-items, all from a list of basic items.

So:

PRODUCT
  product_id PK
, description
, ...

OPTIONAL_ITEM
  option_id PK
, description
, ...

ORDER
  order_id PK
, date_taken...

ORDER_ITEM
  order_id PK, FK
, item_number PK     // Or use a surrogate as PK, if you like
, product_id FK
, quantity

ORDER_ITEM_OPTION
  order_id PK, FK
, item_number, PK, FK  // Order and item FK to ORDER_ITEM
, option_id, PK, FK    // FK to OPTIONAL_ITEM
, quantity

This gives you what you need to have an order with the same item multiple times and 0 or more options applied to each item, as in a coffee with different amounts of cream and sugar etc. as per OP's requirement.

It's not clear what you mean by "each product may have different preferences." Does this mean that each line in an order may have different variations for the same product? So you might buy a product once, a shirt, say, with a size of large, and another time, with a size of medium? In that case, this sounds like products vs. SKUs to me. You would need a "sub-product" or "SKU" table that would be linked to the product table, if that is the case. Then the lines in your order would be linked to SKUs, rather than products. It's possible to link both to the order lines, but not necessary.

Neither of the designs that you showed (as I read them; your notation is a little confusing) is what I would suggest. In situations where OrderID, ProductID does not uniquely identify an order item (as is the case when the same product can be ordered multiple times on a given order), then the common practice is to adopt a surrogate key in the detail table. For example, the schema would look like:

Order (note here that table names, by convention, are in singular form)
----------
OrderID
CustomerID
...
etc.
PK is OrderID

OrderItem
-----------
OrderID
ItemNumber
ProductID
Quantity
...
etc.
PK is OrderID, ItemNumber

ItemNumber would be a sequentially-numbered field that starts over for each OrderID.

As for how to store your preferences, you don't give enough information to answer that adequately. If each individual order item would have exactly one "preference", then you can simply include it in the OrderItem table. If each order item could have a variable number of preferences, then you would need something like:

OrderItemPreference
------------
OrderID
ItemNumber
... preference information
PK is OrderID, ItemNumber, and something to uniquely identify the preference

Here's what I've got in my head:

| OrderId | ProductKey | ProductPrefKey | Qty |

If you have to present the invoice in a certain order, you'll need to add a line identifier, but otherwise you can ignore it. You can make the primary key be OrderId, ProductKey, ProductPrefKey and that will provide you with fast access by order.

Since this seems to be a new implementation, keep in mind the following: prices on products often change over time (as do customer addresses, coupons, etc.), and once you complete an invoice, you'll want to keep a historical record of that. That can be done by changing the ProductKey every time you change any detail about the item, or you'll need some sort of archival system that includes all the historical detail that is relevant to an invoice.