Проблемы со связанными таблицами в зависимости от типа

Question

Я работаю над моделью базы данных для гипотетического парка автомобилей для компании. Есть 4 типа транспортных средств (легковые автомобили, грузовики, полуприцепы и автобусы) и 2 типа водителей. Оба типа могут управлять только одним транспортным средством одновременно, Тип 1 управляет только автомобилями, а Тип 2 может управлять любым транспортным средством каждый день.

Моя модель ER настроена таким образом, что Тип 1 и Тип 2 наследуются из таблицы водителей, а 4 типа транспортных средств наследуются из таблицы транспортных средств. Моя проблема заключается в том, как я могу подключить водителей к их конкретным таблицам транспортных средств в зависимости от их типа.

Answer 1

Настройте VehicleTypes и 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)
) ;

DriverTypes а также 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)
) ;

И, наконец, разрешенные типы транспортных средств, которыми могут управлять различные типы водителей:

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') ;

Если вы также хотите сохранить информацию о том, кто действительно управлял транспортным средством в определенные дни:

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
) ;

Вы можете проверить код в SQL-Fiddle. Диаграмма ER будет выглядеть так:

Answer 2

Похоже, вы думаете с точки зрения ООП, а не реляционных баз данных. Реляционные базы данных на самом деле не поддерживают объектно-ориентированные концепции, такие как наследование. У меня был бы один стол для водителей и один для транспортных средств. Правила о том, какие водители могут управлять, какие транспортные средства являются бизнес-логикой и должны быть определены в коде.

Наличие нескольких таблиц транспортных средств и нескольких таблиц водителей затруднит использование базы данных.