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.

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

  • 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 Mar 25 '17 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 Mar 25 '17 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 Mar 26 '17 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 Mar 28 '17 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 Mar 28 '17 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.

  • 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 Mar 25 '17 at 22:54
  • Yes, theres 3 to pick from, first one uses many nulls (as does previous answer, but on a single table). – arana Mar 27 '17 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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