Results 1 to 5 of 5

Thread: Foreign Keys

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Foreign Keys

    Hi everyone,
    Just need a little help with the design of my database, I have it normalised in 3NF but i am not sure about foreign keys. I dont think there is any but if some could have a quick look that would be great.
    Thanks!

    VehicleDetails (vehicle_Reg, make, model, colour, mileage, photograph, air_con)
    EngineDetails (vehicle_Reg, engine_size, transmission, Four_wheel_drive)
    SalesPeople (Sales_ID, FName, LName, Addr, County)
    VehiclePrice (Sales_ID, cost, List_Price, Asking_Price, Date_bought, Date_sold, Deposit, Deposit_date)
    CustomerDetails (Cust_ID, FName, LName, Addr, County, Country, phone, comments)
    Enquiry (Enquiry_ID, First_Name, Last_Name, Phone, email, question)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if Sales_ID is the PK of SalesPeople, how can it also be the PK of VehiclePrice?

    that would suggest that the VehiclePrice table can contain only one vehicle proce per salesperson, and furthermore, you don't know which vehicle it is

    your data model ~does~ have foreign keys, but you haven't identified any of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have it normalised in 3NF
    No you have not

    >VehicleDetails (vehicle_Reg, make, model, colour, mileage, photograph, air_con)
    >EngineDetails (vehicle_Reg, engine_size, transmission, Four_wheel_drive)
    The 2 tables above can/should be combined into a single table

    You now have a bunch of standalone & unrelated tables with no way to JOIN any two tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, what happens in your CustomerDetails table when you get a home and office number? You only store one or you put customer in as 2 different ids? I think you have quite a bit more work to do here.

  5. #5
    Join Date
    Nov 2009
    Posts
    2
    Ok thanks everyone for your help..
    I have now come up with the following, fairly sure its in 3NF

    •Vehicle— VIN, Make, Model, Color, Mileage, Photograph, Air Conditioning, Size of Engine, Transmission, Four-Wheel Drive, Power Locks, Other Features
    •Salespeople— Sales_ID, Salesperson Name, Salesperson Address, Salesperson Commission
    •Sales Data— Invoice, VIN, CustID, Sales_ID, Cost, List Price, Asking Price, Date Bought, Date Sold, Deposit, Deposit Date
    •Customer— CustID, Customer Name, Customer Address, Customer Phone, Comments

    Fileds in bold are the primary keys and underlined are the foreign keys.
    Thanks in advance
    Last edited by truck; 11-22-09 at 19:53.

Posting Permissions

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