SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: table relations

  1. #1
    SitePoint Enthusiast nkacharani's Avatar
    Join Date
    Oct 2010
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table relations

    Hellos...

    Can someone please help me how to update the data in vehicle table ( i.e. the recordId column, when inserting data into accidentrecord), assuming that data in vehicles where already present in both drivers and vehicle table?


    Code MySQL:
    CREATE  TABLE IF NOT EXISTS `mimi`.`accidentrecord` (
      `recordId` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
      `date_of_occurance` DATE NULL DEFAULT NULL ,
      `timeofday` VARCHAR(10) NULL ,
      `weather` VARCHAR(10) NULL DEFAULT NULL ,
      `location` VARCHAR(45) NULL ,
      `numberOfVehicles` SMALLINT(6) NULL DEFAULT NULL ,
      PRIMARY KEY (`recordId`) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1;


    Code MySQL:
    CREATE  TABLE IF NOT EXISTS `mimi`.`drivers` (
      `driverID` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
      `schoolId` SMALLINT(6) NOT NULL ,
      `driverRegNo` VARCHAR(45) NULL ,
      `surname` VARCHAR(45) NULL DEFAULT NULL ,
      `othernames` VARCHAR(45) NULL DEFAULT NULL ,
      `DOB` DATE NULL DEFAULT NULL ,
      `address` TEXT NULL DEFAULT NULL ,
      `gender` CHAR(1) NULL DEFAULT NULL ,
      `placeOfBirth` VARCHAR(15) NULL DEFAULT NULL ,
      `telephoneNo` VARCHAR(45) NULL DEFAULT NULL ,
      PRIMARY KEY (`driverID`) ,
      ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1;

    Code MySQL:
    CREATE  TABLE IF NOT EXISTS `mimi`.`vehicles` (
      `vehicleID` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
      `recordId` SMALLINT(6) NULL ,
      `driverID` SMALLINT(6) NOT NULL ,
      `vehicleNo` VARCHAR(45) NULL ,
      `insuranceNo` VARCHAR(45) NULL ,
      `vehicleType` VARCHAR(45) NULL DEFAULT NULL ,
      `ownership` VARCHAR(45) NULL DEFAULT NULL ,
      PRIMARY KEY (`vehicleID`) ,
      INDEX `fk_vehicles_accidentrecord1` (`recordId` ASC) ,
      INDEX `fk_vehicles_drivers1` (`driverID` ASC) ,
      CONSTRAINT `fk_vehicles_accidentrecord1`
        FOREIGN KEY (`recordId` )
        REFERENCES `mimi`.`accidentrecord` (`recordId` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_vehicles_drivers1`
        FOREIGN KEY (`driverID` )
        REFERENCES `mimi`.`drivers` (`driverID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that doesn't make sense

    if data is already present in the vehicles table, it must already have a recordId value, yes?

    or else the recordId is NULL

    but in any case, each vehicle can belong to only one accidentrecord

    i think maybe you need to rethink the table relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast nkacharani's Avatar
    Join Date
    Oct 2010
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have set that recordId can be NULL in vehicle table and accidentrecord has a one-to-many relation to the vehicle table

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nkacharani View Post
    ... accidentrecord has a one-to-many relation to the vehicle table
    yes, that makes sense, if an accident involves more than one vehicle

    but can a vehicle be involved in more than one accident?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast nkacharani's Avatar
    Join Date
    Oct 2010
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think its possible for one car to be in more than one accident if they are not happening at the same time. But on the second thought to simplify the querying I think it is best to have a one-to-one relation. Now I am working on the second thought but seem to get stacked, any help

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If a vehicle is involved in two accidents, would there not be a separate accident record for each collision?

    So you can have a number of collisions records involving the same vehicle.

    To me, that means

    a table for accident records
    a table for vehicles
    a many-to-many table for accidents_vehicles.
    a table for drivers
    a many-to-many table joining drivers to vehincles, given that a driver may have more than one car either simultaneously or in a lifetime.

    In your accident_record table, you might find it better not to show number of vehicles involved. May be better to have query on the m2m table accidents_vehilces, to get the details of the vehicles involved?

    And at the risk of over-thinking this, you might not want to store a driver id next to a vehicle, given that a vehuicle has more than one owner/driver in a lifetime and more than one driver during a single term of ownership.

    OK, I'll stop there in case I have confused you or suggested you go down the wrong road.

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    If a vehicle is involved in two accidents, would there not be a separate accident record for each collision?
    possibly... but in that case, which of the accident records gets logged in the vehicle row? there's only the one FK there, so, what, it would be the first accident? the latest?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast nkacharani's Avatar
    Join Date
    Oct 2010
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    Quote Originally Posted by r937 View Post
    possibly... but in that case, which of the accident records gets logged in the vehicle row? there's only the one FK there, so, what, it would be the first accident? the latest?


    See I thought of that too!..now I think maybe I should be adding the drivers details after occurance of accident, in the sense that now I have:
    -a one-to-many relation between the accident table and drivers table, i.e a record can have more than one driver.
    -a one-to-one relation between drivers and vehicle, because a driver can be in only one car when the accident happen.

    Now I am facing one challenge:- say the accident involved two cars(means there are two drivers). how will I add at the same time the details of both drivers from the user interface.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nkacharani View Post
    ...how will I add at the same time the details of both drivers from the user interface.
    it is tempting to start thinking of how the user interface should be constructed, to input and/or update the data...

    but you must resist this temptation

    at this point, it is important to decide what you want to record in the database, including the relationships, and worry about the "how" later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast nkacharani's Avatar
    Join Date
    Oct 2010
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically I wanted to have the drivers registered in the systems and update the accident record for a particular driver found in the database whenever they cause an accident.


Bookmarks

Posting Permissions

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