Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    .
    Posts
    1

    Unanswered: Sorry- Please read- normalisation

    sorry if i have posted this in the wrong forum but i really need help normalising my tables to the 3NF .i have tried learning but i can't get my head round it. would anyone be able to show me using my tables.
    Account sheet
    ·Account ID--- Character
    ·Contact ID--- Character
    ·Date --- Character
    ·Quantity (order 1)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Quantity (order 2)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Quantity (order3)--- Integer
    Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    .Quantity (order 4)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Total Trucks--- Integer
    ·Payment Method--- String
    ·Total--- Real
    ·Price par Month (inc. VAT)--- Real
    ·Grand Total (inc. VAT)--- Real

    Please. thanks

  2. #2
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Re: Sorry- Please read- normalisation

    Originally posted by BigBen_01
    sorry if i have posted this in the wrong forum but i really need help normalising my tables to the 3NF .i have tried learning but i can't get my head round it. would anyone be able to show me using my tables.
    Account sheet
    ·Account ID--- Character
    ·Contact ID--- Character
    ·Date --- Character
    ·Quantity (order 1)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Quantity (order 2)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Quantity (order3)--- Integer
    Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    .Quantity (order 4)--- Integer
    ·Truck Name--- String
    ·Model--- Character
    ·Price--- Real
    ·Total Trucks--- Integer
    ·Payment Method--- String
    ·Total--- Real
    ·Price par Month (inc. VAT)--- Real
    ·Grand Total (inc. VAT)--- Real

    Please. thanks

    AS I see it you need:

    Contacts Table
    ContactID
    ContactName
    other contact fields.....etc


    Orders Table:
    OrderID
    ContactID
    PaymentMethodID
    PayedAmount
    DateOfOrder




    OrderDetail Table
    OrderID
    ProductID
    etc........

    Products Table
    ProductID
    ProductName
    ProductPrice
    ProductDescription
    etc........

    PaymentMethod Table
    PaymentMethodID
    PayMethodName

    Never store calculatable data ie totals, price per month, etc these are things that should be done at run time for reporting or display to the user. Hope this helps
    the light is on, someone is home, but they dont know they are. HELLO!

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    I agree with dapman2002 (as far as table structure), but will disagree 50% of the time when it somes to storing calculated values. There are a few reasons to store calculated values.

    1. Items where sold at a price, stated in the inventory table, and 3 months later you change the price. The next time you calculate the price it is based on current pricing and not the price 3 months before (bare in mind that this situation assumes that you don't need or want to store the sales price of that transaction, but you get my meaning).
    2. I have also run into situations where I have been forced to store some caluclated data to speed up the creation of some reports to met user requirements on how long they have to wait for a report to show or be printed. (this company had a 30 second time limitation on waiting for a report and hardware speed was not controllable)

    Now I recognize that means the datbase in not in 3NF, but 3NF may not the prefect state for all situations.

    S-

  4. #4
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by sbaxter
    I agree with dapman2002 (as far as table structure), but will disagree 50% of the time when it somes to storing calculated values. There are a few reasons to store calculated values.

    1. Items where sold at a price, stated in the inventory table, and 3 months later you change the price. The next time you calculate the price it is based on current pricing and not the price 3 months before (bare in mind that this situation assumes that you don't need or want to store the sales price of that transaction, but you get my meaning).
    2. I have also run into situations where I have been forced to store some caluclated data to speed up the creation of some reports to met user requirements on how long they have to wait for a report to show or be printed. (this company had a 30 second time limitation on waiting for a report and hardware speed was not controllable)

    Now I recognize that means the datbase in not in 3NF, but 3NF may not the prefect state for all situations.

    S-
    Good points well made.

    Dap
    the light is on, someone is home, but they dont know they are. HELLO!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by sbaxter
    Now I recognize that means the datbase in not in 3NF...
    actually, storing a calculated or summary value does not automatically disrupt third normal form

    3nf simply means each column depends on the entire primary key, and all non-key columns are mutually independent

    storing the total price of an order calculated at the time of the order is still 3nf as far as i can see

    rudy
    http://r937.com/

Posting Permissions

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