CODEWITHSUNDEEP
database-designrelational-database
user7471661
user7471661

Reputation:

Relational Database Table, data duplicity risk?

I'm trying to create a hypothetical database for an airplane charter company and I'm a little bit stuck on how many tables I should create for employees... Here is a basic introduction to the problem:

UML Diagram for the Relational Database

The company hires many employees, employees consist of crew members and non-crew members, crew members are pilots, flight attendants, flight engineers and loadmasters, non-crew members can be managers, receptionists and so on. Not all employees are crew members.

All crew members are required to take frequent medical tests and records of these tests need to be stored. All crew members are also required to have licenses and certifications, details of these licenses and certifications need to be stored.

When a flight is booked, at least one crew member is assigned to the booking, depending on the type of aircraft and type of booking. So if it's a small aircraft, then a single pilot is the only crew member. However if it's a jet, then two pilots and flight attendants form the crew.

Now my problem is this, should I create a table full of employees that consist of crews and non crews, and another two tables for Licenses/Certifications and Medical Tests? (Relating employees table with licenses/certification and medical tests tables)

Or

Should I create a basic table with all the employees and a separate table for crew members, assigning licenses/certification and medical tests tables to only the crew members table? In this way, crew members are separated from non-crew members, so the relation of crew members and Medical and Certification tables can be separated from the whole employee table. I'm worried about data duplicity here.

Many thanks in advance!

Upvotes: 0

Views: 3668

Answers (1)

SMor
SMor

Reputation: 2882

"Should I" is a question that cannot really be answered without a good understanding of your model and how you intend to use it. So the first question is why you aren't simply implementing the model presented in the diagram? The diagram suggests the first option is intended. However, the dependent tables are rather murky in their intent/usage.

  • Not all crew members are required to take the same tests and hold the same certifications.
  • anytime you see a "flag" column (license_or_test) in a table you have a problem IMO.
  • Based on the diagram, the primary key of Crew is the same as Employee. That should flow through the dependent tables as well.
  • If a booking is associated with a specific customer, there is no need to include company_id in the table.
  • If Customers has a FK to Company, why doesn't Aircraft?
  • You discuss flight but have nothing to represent it in your schema. Perhaps that is what you meant with Bookings? See next item.
  • Regardless of your decision, BE CONSISTENT. You use plural names for your tables - except for crew. You use the term crew member as well. Flight vs. bookings. etc.

And I will add that you have oversimplified your flight-dependent schema. For a given flight, you will have a "crew" that consists of specific members. Usually this will consist of multiple employees (pilot, copilot, etc). Withing getting into licensing issues, a pilot can function as a copilot on a flight. I don't understand why Employee_ID is in Bookings - but maybe you intend a different usage there.

I suggest you pick an approach and see where it takes you. Or do both. You will learn more by jointly working with different schemas that represent the same model.

Upvotes: 1

Related Questions