3

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

2

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.

1

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like:

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

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.