Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    records with quantity and relations

    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •