Results 1 to 13 of 13

Thread: My database

  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: My database

    http://img16.imageshack.us/img16/3782/databasemq.jpg

    I am trying to give the DeliveryDetails.QuantityDelivered column a default value of ProductOrder.QuantityOrdered

    how do i do this??

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Use an INSERT trigger
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by caa5042 View Post
    I am trying to give the DeliveryDetails.QuantityDelivered column a default value of ProductOrder.QuantityOrdered
    there's your problem right there -- you shouldn't want that

    at what point in the order fulfillment process are the DeliveryDetails rows created?

    after the order was delivered? or before?

    if before, i'm wondering why, because, you know, it hasn't happened yet

    if after, then i'm wondering why there has to be a default, i mean, if you're recording the delivery details why wouldn't you know the delivery amount?


    sigh

    in any case, at the point in the application logic where you know the ProductID and the OrderID (and you have to know both, in fact you are likely reading ProductOrder rows to get them), you can easily grab ProductOrder.QuantityOrdered and just use it when you insert the DeliveryDetails row

    the trigger would just be extra processing and hard to write and test

    but like i said, it sounds like you're creating DeliveryDetails rows ~before~ delivery, and that just doesn't seem right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh wait, i had a second thought -- you're probably just building the ER model now, and don't even have a working application yet

    just make the default value for DeliveryDetails.QuantityDelivered NULL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    39
    what does ER mean? Im going to be working on the asp.net application in a few days so i need this database to be right...so please be very critical and dont assume im just practicing.....

    Well i want my database to support partial deliveries....like you can deliver a couple of items at a time until everything is delivered.....and you're right...delivery happens after the order...but the default values will not show up in delivery until someone inserts a record in the deliverydetails table right???

    if you have suggestions on how i can model delivery in parts please let me know

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ER = entity-relationship

    delivery in parts is easily done, by inserting only those products into the DeliveryDetails table which actually have been delivered
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2011
    Posts
    39
    alright...

    ok how do i apply a check constraint to the deliverydetails table stating that the number of DeliveryDetails.ProductA delivered cannot be greater that the the number of ProductOrdered.ProductA ordered ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "dear customer, just a note to explain today's delivery, we have a special promotion just for today, we realize you only ordered 2 widgets, but seeing as how you are a preferred customer, you qualify for our instant 'get one free' promotion, so we have delivered 3 widgets to you for the same price"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2011
    Posts
    39
    i dont get it

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you want the number of DeliveryDetails.ProductA delivered to be not greater than the number of ProductOrdered.ProductA ordered?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2011
    Posts
    39
    hmm...well if items are delivered you dont want to mistakenly type in a value thats greater than the number ordered right?...

    it seems it is very common to have a check constraint that references a separate table...but i cant figure it out....

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not mistakenly, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2011
    Posts
    39
    how about my design?...do you recommend linking up ProductOrder with DeliveryDetails? instead of linking it to both the Product and Order tables?.and how about ProductDiscount table? Should it be linked to ProductOrder too?

Posting Permissions

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