Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: database structure

    Hi,
    I have a question. I want to build a db for the following entities:
    drivers
    cars
    companies

    each driver nay has a car (NULL or carId)
    each driver belongs to company
    each car belongs to company

    The db structure I built is:
    car(carId, companyId)
    driver(driverId, companyId, carId)
    company(companyId)

    The problem is that in this structure driver from company a can have a car from company b.

    but this situation is not possible.. How can I prevent such situation??

    tnx

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can a driver have more than one car?
    Can a car have more than on driver?
    Apparently, not every driver has a car. Does every car have at least one driver?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2008
    Posts
    3

    explanation

    each car can has one driver at the same time.
    driver can have only one car at the same time
    there can be cars without driver

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then your structure looks fine. To ensure that a driver from company A is not assigned a car from company B, put a trigger on the drivers table for inserts and updates to verify the data before the transaction is committed.
    For good form, you could put a trigger on the Cars table as well just in case a car is moved from one company to another.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can't put a trigger on a homework question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2008
    Posts
    3

    I prefer not to use triggers

    I don't like "secret actions".
    I want to know what is running without writing down where do I haave triggers.

    Is there any other way to do so? using constraints or something?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One additional table, four foreign keys, and one check constraint can get you what you want. Boy, would it be ugly, but it'll work.

    -PatP

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    your instinct away from using triggers is a good one - they are often misused (send an email whenever a row gets updated for example).

    in this case though I think triggers are appropriate.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How is a trigger hanging off of a table any more "secret" than a constraint hanging off of a table.
    You got the advice you were seeking, from a DBA with 15 years of professional experience. Take it or leave it.
    Good Lord...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need four tables: Company, Car, Driver, and CarDriver. Company is a strong entity, the others are all weak. The CHECK constraint needs to be in CarDriver. No triggers are needed in this solution. The remainder of this assignment is left as an exercise for the reader.

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Software packages (even ERD modeling tools) usually can't read triggers. They can usually read constraints just fine. While they can often detect a trigger, they usually can't determine what the trigger does, which makes the trigger's function much more "secret" than a constraint for all practical purposes... An experienced human being can determine if a trigger does what is logically required, while even garden grade software can usually tell what a constraint does.

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, yes, good old "EFTS" (exercise for the student)

    i got that a lot in my math courses, thanks for the fond memory
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    You need four tables: Company, Car, Driver, and CarDriver....No triggers are needed in this solution.
    I think I see what you are getting at. Clever. Composite foreign keys?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I guess you need CarCompanyID and DriverCompanyID in the CarDriver table to make this work, with a check constraint on them to force them to be equal? and two composit FKs.

    If these cols are not in CarDriver, I don't see how else you would enforce the rule that cars and their drivers must be from the same company.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Proving once again there are more ways to skin a cat than there are cats, but that's no reason to stop trying!

    Yes, both Car and Driver depend on Company for their very definition... Without a Company, neither of them can exist, so that implies that the CompanyID needs to be part of their Primary Key.

    Since the CarDriver would have an FK for both the Car and the Driver, it would also inherit the company ID values too. You could then add a CHECK constraint, and voila!

    -PatP

Posting Permissions

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