Database Design (how to handle subclass & superclass)

database-designnormalization

I'm wondering what is the best approach to this type of problem (I'm a highschooler so don't have practical knowledge on the subject):

Let's say I want to store information about employees at a zoo. Some employees are zookeepers and take care of animals. Other employees are in charge of cleaning up, while others are in charge of delivering goods from one place to another.

Let's say each type of employee shares some attributes, like first_name, last_name, date_of_birth, but other attributes are dependent on the type of employee, like for delivery people, I would want to know if they a commercial drivers' license. For zookeepers, I would want to know what certifications they have on taking care of animals.

Am I better off creating separate entities for each type of employee, like "zookeeper", "driver" , "cleanup_dude", and having appropriate columns for each?

Or am I better off creating a single "employee" entity, and adding a column to indicate the type of employee, and other column flags for things like "has_CDL", "has_AnimalHandlingCert", etc. These would be left null for employees that do not match the criteria.

Also certain types of employees could have relationships with certain tables, which may or may not be shared with other employees. Like zookeepers could be handling different sections of the zoo, which I would want to store, but the same would not be true for a cleanup guy.

Not sure what would be the best approach to this. Any advice?
Thanks

Best Answer

I've just recently answered a similar question here. What you want is a People table containing data common to all people contained in your DB with other subsidiary tables providing the additional data unique to different roles some of those people play in the operation of your service. The linked question and answer should provide the details you need. If not, a couple of my answers to related questions can be found here and here. However, perform a search to get a variety of answers from other contributers. I have found many times that the best answer for a particular situation may be a combination of techniques from several sources.

Related Solutions

Approach to Analyzing Database Requirements

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example:

Person
------
  id
  ref_num
  reg_dt
  addrs_line1
  addrs_line2
  postal_code
  phone_num

th_person
--------
  id
  person_id (FK to person.id)
  max_accepted_at_one_time

animals
-------
  id
  accepted_by_th_person (FK to th_person.id)

ph_person
---------
  id
  person_id (FK to person.id)
  animal_ref_num

cs_person
---------
  id
  person_id (FK to person.id)

donations
---------
  id
  date
  amount
  donor_id (FK to cs_person.id)

This might be a good starting point.

Best relationship database design to manage types for different entities

If your various types of individuals and organizations (employees, clients, suppliers) each have multiple phone numbers of various types, then you want to keep the phone numbers in a child table.

This child table should be an intersection between the individual/organization and a phone type table.

Since you have different allowable phone types for each type of legal entity, you can create a table for each of these lists of allowable phone types (your Case 1). The only issue with this is that you may have duplication of data, for example having a "mobile" record in multiple allowable types tables. There is a way to have distinct lists of allowable phone types while at the same time rationalizing these lists for management purposes.

Consider this ERD:

ERD

There is one master lists of phone types (PHONE_TYPE) which includes flags for each possible legal entity type, indicating whether a particular phone type is allowable for each type of legal entity. The table might look like this:

create table PHONE_TYPE
( phone_type_id int primary key
, description varchar(10)
, is_client_type bit
, is_employee_type bit
, is_supplier_type bit
);

From this master list of phone types, you can create distinct views for each legal entity phone type list (i.e. CLIENT_PHONE_TYPE, EMPLOYEE_PHONE_TYPE, SUPPLIER_PHONE_TYPE). One of these views might look like this:

create view CLIENT_PHONE_TYPE
as
select * from PHONE_TYPE
where is_client_type = 1;

This lets you maintain the phone types master list in one place while enforcing the list of allowable phone types for each of the different types of legal entity that you support.

Best Answer

Related Solutions

Approach to Analyzing Database Requirements

Best relationship database design to manage types for different entities

Related Question