How to implement this data structure in SQL tables

Question

I have a problem that can be summarized as follow:

Assume that I am implementing an employee database. For each person depends on his position, different fields should be filled. So for example if the employee is a software engineer, I have the following columns:

Name
Family
Language
Technology
CanDevelopWeb 

And if the employee is a business manager I have the following columns:

Name
Family
FieldOfExpertise
MaximumContractValue
BonusRate

And if the employee is a salesperson then some other columns and so on.

How can I implement this in database schema?

One way that I thought is to have some related tables:

CoreTable:

Name
Family
Type

And if type is one then the employee is a software developer and hence the remaining information should be in table SoftwareDeveloper:

Language
Technology
CanDevelopWeb 

For business Managers I have another table with columns:

FieldOfExpertise
MaximumContractValue
BonusRate

The problem with this structure is that I am not sure how to make relationship between tables, as one table has relationship with several tables on one column.

How to enforce relational integrity?

Answer 1

There are a few schools of thought here.

(1) store nullable columns in a single table and only populate the relevant ones (check constraints can enforce integrity here). Some people don't like this because they are afraid of NULLs.

(2) your multi-table design where each type gets its own table. Tougher to enforce with DRI but probably trivial with application or trigger logic.

The only problem with either of those, is as soon as you add a new property (like CanReadUpsideDown), you have to make schema changes to accommodate for that - in (1) you need to add a new column and a new constraint, in (2) you need to add a new table if that represents a new "type" of employee.

(3) EAV, where you have a single table that stores property name and value pairs. You have less control over data integrity here, but you can certainly constraint the property names to certain strings. I wrote about this here:

• What is so bad about EAV, anyway?

Answer 2

No existing SQL engine has solutions that make life easy on you in this situation.

Your problem is discussed at fairly large in "Practical Issues in Database Management", in the chapter on "entity subtyping". Commendable reading, not only for this particular chapter.

The proper solution, from a logical design perspective, would be similar to yours, but for the "type" column in the core table. You don't need that, since you can derive the 'type' from which non-core table the employee appears in.

What you need to look at is the business rules, aka data constraints, that will ensure the overall integrity (aka consistency) of the data (of course whether any of these actually apply is something your business users, not me, should tell you) :

Each named employee must have exactly one job, and thus some job detail somewhere. iow : (1) no named employees without any job detail whatsoever and (2) no named employees with >1 job detail.

(3) All job details must be for a named employee.

Of these, (3) is the only one you can implement declaratively if you are using an SQL engine. It's just a regular FK from the non-core tables to the core table.

(1) and (2) could be defined declaratively in standard SQL, using either CREATE ASSERTION or a CHECK CONSTRAINT involving references to other tables than the one the CHECK CONSTRAINT is defined on, but neither of those constructs are supported by any SQL engine I know.

One more thing about why [including] the 'type' column is a rather poor choice to make : it changes how constraint (3) must be formulated. For example, you can no longer say "all business managers must be named employees", but instead you'd have to say "all business managers are named employees whose type is <type here>". Iow, the "regular FK" to your core table has now become a reference to a VIEW on your core table, something you might want to declare as, say,

CREATE TABLE BUSMANS ... REFERENCES (SELECT ... FROM CORE WHERE TYPE='BM');

or

CREATE VIEW BM AS (SELECT ... FROM CORE WHERE TYPE='BM'); CREATE TABLE BUSMANS ... REFERENCES BM;

Once again something SQL doesn't allow you to do.

Answer 3

To further expand on your one way with the core table is to create a surrogate key based off an identity column. This will create a unique employee id for each employee (this will help you distinguish between employees with the same name as well).

The foreign keys preserve your referential integrity. You wouldn't necessarily need EmployeeTypeId as someone else mentioned as you could filter on existence in the SoftwareDeveloper or BusinessManagers tables. The column would instead act as a cached data point for easier querying.

You have to fill in the types in the below sample code and rename the foreign keys.

create table EmployeeType(
    EmployeeTypeId
,   EmployeeTypeName
,   constraint PK_EmployeeType primary key (EmployeeTypeId)
)

create table Employees(
    EmployeeId int identity(1,1)
,   Name
,   Family
,   EmployeeTypeId
,   constraint PK_Employees primary key (EmployeeId)
,   constraint FK_blahblah foreign key (EmployeeTypeId) references EmployeeType(EmployeeTypeId)
)

create table SoftwareDeveloper(
    EmployeeId
,   Language
,   Technology
,   CanDevelopWeb
,   constraint FK_blahblah foreign key (EmployeeId) references Employees(EmployeeId)
)

create table BusinessManagers(
    EmployeeId
,   FieldOfExpertise
,   MaximumContractValue
,   BonusRate
,   constraint FK_blahblah foreign key (EmployeeId) references Employees(EmployeeId)
)

Answer 4

You are describing one ("class per table") of the 3 possible strategies for implementing the category (aka. inheritance, generalization, subclass) hierarchy.

The correct "propagation" of PK from the parent to child tables is naturally enforced by straightforward foreign keys between them, but ensuring both presence and the exclusivity of the child rows is another matter. It can be done (as noted in the link above), but the added complexity is probably not worth it and I'd generally recommend handling it at the application level.

Answer 5

You are on the right track. You can set up PK/FK relationships from the general person table to each of the specialized tables. You should add a personID to all the tables to use for the relationship as you do not want to set up a relationship on name because it cannot be a PK as it is not unique. Also names change, they are a very poor choice for an FK relationship as a name change could cause many records to need to change. It is important to use separate tables rather than one because some of those things are in a one to many relationship. A Developer for instnce may have many differnt technologies and that sort of thing should NEVER be stored in a comma delimted list.

You could also set up trigger to enforce that records can only be added to a specialty table if the main record has a particular personType. However, be wary of doing this as you wil have peopl who change roles over time. Do you want to lose the history of wha the person knew when he was a developer when he gets promoted to a manager. Then if he decides to step back down to development (A frequent occurance) you would have to recreate his old record.

Answer 6

1. I would add a field called EmployeeId in the EmployeeTable
2. I'd get rid of Type
3. For BusinessManager table and SoftwareDeveloper for example, I'll add EmployeeId
4. From here, you can then proceed to create Foreign Keys from BusinessManager, SoftwareDeveloper table to Employee

Answer 7

You can use all fields in the same table, but you'll need an extra table named Employee_Type (for example) and here you have to put Developer, Business Manager, ... of course with an unique ID. So your relation will be employee_type_id in Employee table.

Using PHP or ASP you can control what field you want to show depending the employee_type_id (or text) in a drop-down menu.