Issues with relating tables depending on a type

Question

I'm working on a database model for a hypothetical fleet of vehicles for a company. There's 4 types of vehicles (cars, trucks, semi-trucks and buses) and 2 types of drivers. Both types can only drive one vehicle at a time, with Type 1 driving only cars, and Type 2 being able to drive any one vehicle each day.

I have my ER model set up in a way that Type 1 and Type 2 are inherited from the Driver table, while the 4 vehicle types are inherited from the Vehicle table. My problem lies in the way I can connect the Drivers to their specific vehicle tables depending on their type.

ypercubeᵀᴹ · Accepted Answer · 2013-01-21T21:47:40.687

Set up the VehicleTypes and the Vehicles:

CREATE TABLE VehicleType
( VehicleTypeID CHAR(1) NOT NULL
, VehicleTypeName VARCHAR(20) NOT NULL
, PRIMARY KEY (VehicleTypeID)
, UNIQUE (VehicleTypeName)
) ;

INSERT INTO VehicleType
  (VehicleTypeID, VehicleTypeName)
VALUES
  ('C', 'Car'),
  ('T', 'Truck'),
  ('S', 'Semi-truck'),
  ('B', 'Bus') ;

CREATE TABLE Vehicle
( VehicleID INT IDENTITY(1,1)
, VehicleTypeID CHAR(1) NOT NULL
, LicencePlate VARCHAR(20) NOT NULL
--- other stuff about a vehicle
, PRIMARY KEY (VehicleTypeId, VehicleID)
, UNIQUE (LicencePlate) 
, FOREIGN KEY (VehicleTypeId)
    REFERENCES VehicleType (VehicleTypeId)
) ;

The DriverTypes and Drivers:

CREATE TABLE DriverType
( DriverTypeID INT NOT NULL
, DriverTypeTitle VARCHAR(20) NOT NULL
, PRIMARY KEY (DriverTypeID)
, UNIQUE (DriverTypeTitle)
) ;

INSERT INTO DriverType
  (DriverTypeID, DriverTypeTitle)
VALUES
  (1, 'Driver-Type-1'),
  (2, 'Driver-Type-2') ;

CREATE TABLE Driver
( DriverID INT IDENTITY(1,1) NOT NULL
, DriverTypeID INT NOT NULL
, FullName VARCHAR(40) NOT NULL
--- other stuff about a driver
, PRIMARY KEY (DriverTypeID, DriverID)
, FOREIGN KEY (DriverTypeID)
    REFERENCES DriverType (DriverTypeID)
) ;

And finally the allowed types of vehicles that the various types of drivers can drive:

CREATE TABLE Driver_Vehicle_Combinations
( DriverTypeID INT NOT NULL
, VehicleTypeID CHAR(1) NOT NULL
, PRIMARY KEY (DriverTypeID, VehicleTypeID)
, FOREIGN KEY (DriverTypeID)
    REFERENCES DriverType (DriverTypeID)
, FOREIGN KEY (VehicleTypeId)
    REFERENCES VehicleType (VehicleTypeId)
) ;

INSERT INTO Driver_Vehicle_Combinations
  (DriverTypeID, VehicleTypeID)
VALUES
  (1, 'C'),
  (2, 'C'),
  (2, 'T'),
  (2, 'S'),
  (2, 'B') ;

If you also want to to store who actually drove which vehicle at specific days:

CREATE TABLE Driver_Schedule
( DriverID INT NOT NULL
, DriverTypeID INT NOT NULL
, ScheduleDate DATE NOT NULL
, VehicleID INT NOT NULL
, VehicleTypeID CHAR(1) NOT NULL
, PRIMARY KEY (DriverID, ScheduleDate)
, UNIQUE (VehicleID, ScheduleDate)         --- If a vehicle is allowed to be
                                           --- driven by only one driver each day
, FOREIGN KEY (DriverTypeID, DriverID)
    REFERENCES Driver (DriverTypeID, DriverID)
, FOREIGN KEY (VehicleTypeId, VehicleID)
    REFERENCES Vehicle (VehicleTypeId, VehicleID)
, FOREIGN KEY (DriverTypeID, VehicleTypeId)      --- this ensures than no driver
    REFERENCES Driver_Vehicle_Combinations       --- gets to drive a vehicle that
              (DriverTypeID, VehicleTypeId)      --- is not allowed to
) ;

You can test the code at SQL-Fiddle. The ER diagram would look like this:

ER diagram

score 0 · Answer 2 · answered Jan 21 '13 at 16:37

0

It looks like you are thinking in terms of OOP not relational databases. Relational databases don't really support object oriented concepts such as inheritance. I would have one table for drivers and one for vehicles. The rules about which drivers can drive which vehicles are business logic and should be defined in code.

Having multiple vehicle tables and multiple driver tables is going to make using the database very difficult.

answered Jan 21 '13 at 16:37

Peter Wooster

6,009
2
27
39

And is there no way to make it be conditional if I change the Type from table to attribute? – iVikD Jan 21 '13 at 16:42

Issues with relating tables depending on a type

2 Answers2