How to model a database to retain driver related information

database-design

I am modelling a chauffeur-like system, where for each driver that registers in the platform, he/she can either be a "solo" driver or belong to an already associated company.

Independently of the type of driver, the following information is registered:

  • Driver ID (Primary Key – I considered using this parameter in order to have the system more "ordered")
  • Name
  • Email (UNIQUE)
  • Phone Number (UNIQUE)
  • Registration Date (Time Stamp)

Now, if it's a "solo" driver, the following additional information will also be asked:

  • Vehicle type (for example: 'car', 'motorbike', etc.)
  • Plate number (UNIQUE)
  • Fiscal number (UNIQUE)

If he/she belongs to an associated company, only the name of the company will be asked (since all the vehicles will be associated to the car company).

The company table will have the following attributes:

  • Company ID (Primary Key – I considered using this parameter in order to have the system more "ordered")
  • Name
  • Address
  • Contact (UNIQUE)
  • Email (UNIQUE)
  • IBAN (UNIQUE)
  • Fiscal Number (UNIQUE)

Lastly, the vehicle table will have the following attributes:

  • Vehicle ID (Primary Key – I considered using this parameter in order to have the system more "ordered")
  • Company ID (Foreign Key)
  • Vehicle Type
  • Plate Number (UNIQUE)

A vehicle cannot exist as a standalone entity, i.e., it must be associated to a driver or to an existing company.

I would like to model this scenario using a relational DB since this info will be coupled with a routing algorithm. I'm having a difficult time finding a "clean" solution for this problem (specially considering that we are storing related-entity information, vehicle, in two different tables)… so, what's the best way to model this situation?

Responses to clarification requests

  1. In your business domain, is it possible for a solo driver to eventually work for a car company?
  2. Is it possible for a driver that works for a car company to become a solo driver only?
  3. Can a driver be working for a car company and, at the same time, be working as well as a solo dirver?

Regarding questions 1 and 2, those are cases I haven't quite considered, but they seem plausible possibilities, so we can consider that those are possible scenarios.

Regarding question 3, for the moment, let's consider that a person can only be a solo driver or a company driver.

Best Answer

Assuming there is a 'company' table as well that drivers can be associated to?

If so, one option is as follows:

Driver:

  1. Driver_ID (PK)
  2. Vehicle_ID (FK to Vehicle... nullable)
  3. Company_ID (FK to Company... nullable)
  4. solo_driver (boolean value [kind of optional, but could be useful])
  5. other Attributes...

Vehicle:

  1. Vehicle_ID (PK)
  2. Company_ID (FK to Company... nullable)
  3. Other Attributes...

Company:

  1. Company_ID (PK)
  2. Other Attributes...

If the driver is a solo-driver, then they have an associated Vehicle_ID If the driver is a company-driver, then they have an associated Company_ID - The company_ID would have associated (one-to-many) vehicle_ID's

Related Question