5

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.

2
  • What you want is called "The Party Model". It uses table inheritance to allow you to point foreign keys at individuals or organizations Commented Mar 25, 2017 at 6:49
  • 1
    Let us continue this discussion in chat.
    – Marvin
    Commented Mar 28, 2017 at 19:05

2 Answers 2

1

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

5
  • 2
    That seems a great idea! In terms of good practice, is it possible that in allowing FK to have the NULL value it would have a negative impact on terms of system performance?
    – Marvin
    Commented Mar 25, 2017 at 20:06
  • 2
    Moreover, in the Driver table, since a solo driver has more attributes than a driver that is associated with other company, what value would those attributes have when I'm registering a company driver? They are set to NULL?
    – Marvin
    Commented Mar 25, 2017 at 22:58
  • FK having NULLs shouldn't have impact on performance. The key is to make sure you have good indexes built on the tables. Having NULL values is normal in many database systems. The values in the Driver table would be NULL.
    – Wes
    Commented Mar 26, 2017 at 4:01
  • 2
    what about creating another table whose attributes are the ones required for the individual driver and in the driver table we leave only the common attributes between the two types of driver? Do you think it would bring some benefits to the architecture of the system?
    – Marvin
    Commented Mar 28, 2017 at 13:58
  • On first thought, I don't think there would be a huge benefit. You would end up (almost) always joining it anyways causing more action to occur in your execution plans... this would slow down query performance (possibly unnoticeable though.) Don't be afraid of NULL. :D
    – Wes
    Commented Mar 28, 2017 at 14:32
0

The most common designs for this scenario are:

  1. A single table with all columns, some of them will be NULL depending on type of chauffeur.

  2. One table for each type of chauffeur.

  3. A Main table for common data and additional tables for each type containing only unique columns specific for each type.

If number of chauffeurs is not really high, and since you have only 2 types of chauffeurs/specific column sets I would consider the first option, easier to maintain and no joins.

In Number 3 it might be easier to add new types if you need to, just adding one more table and not altering your previous ones.

2
  • I was trying to avoid a solution like that since it's fairly based on an (excessive) usage of NULL...don't you think there's a more "elegant" solution?
    – Marvin
    Commented Mar 25, 2017 at 22:54
  • Yes, theres 3 to pick from, first one uses many nulls (as does previous answer, but on a single table).
    – arana
    Commented Mar 27, 2017 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.