I have a table ORDER_LINE, the key is (order-id, product-id), and their is a field called "quantity".

Problem is, I now have something that refers to the individual items. So if you ordered 5 blankets (for example), I need to have one record per blanket (potentially).

It's a little strange, since the individual blankets don't have an identity as such. I.E. you're not ordering a specific blanket, you just want 5. The new table says "3 need to be giftwrapped with X, 2 with Y".

I ended up with a table with key (order-id, product-id, giftwrap-id) and a field quanitity.

Does that make sense? It seems a little weird to me, since there is no RI to keep the quantities in synch.

You could have a small trigger that ensures that the sum(quantity) [based on new table] <= quantity based on order_line table (with the appropriate where clauses)

Ravi

It may also be part of the evolution of your database to recognize each order item uniquely and abolish the quantity attribute. Surrogate keys would help with this transition.

I'd actually considered that. It poses a few trade-offs:

- each item doesn't really have anything that uniquely identifies it, so as you said we'd be talking about a surogate key.

- high quantities = lots of rows. Not a major problem at the moment since we're generally dealing with low quantities.

- most of the time the data will be accessed as order lines, not items. I could solve this easily enough by having a view, though.

I'm not entirely sure which I'll go with, they both have drawbacks and advantages. I think having "order item" is more elegant and gives me the RI I want, but "order line" is a bit more intuitive since that's how things appear on invoices and such.

Thanks for the input.