Table relations

Databases
1

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?

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;


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;

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

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 :slight_smile:

3

I have set that recordId can be NULL in vehicle table and accidentrecord has a one-to-many relation to the vehicle table

4

yes, that makes sense, if an accident involves more than one vehicle

but can a vehicle be involved in more than one accident?

5

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

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

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?

:slight_smile:

8

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

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

10

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.