4

Description of what I have to do

I have a table that should be related to Table1 OR Table2 OR Table3

For instance, there's a table Employees and it has: Id, Name, Address, Age, Salary, EmployerId

The second table is RegisterEmployeeRequirements: Id, RequirementType, EmployerId, EntryId.

Where requirement type could be CreditStatusRequirement or EmployeeDegreeRequirement).

The Problem: CreditStatusRequirement includes both the CreditStatus and the date it was acquired (to check if it was in the last year). I also have additional table which is named CreditStatusRequirements with columns:

CreditStatus, DateTimeAcquired

On the other hand, the degree requirement which has the following properties: DegreeName and MinGpa.

To solve this I created another table with these properties. If the requirement type in the RegisterEmployeeRequirements is CreditStatusRequirement I will use the entryId column to look at the CreditStatusRequirements table and then to check if it is completed.

Otherwise, if it is EmployeeDegreeRequirement, I will use the entryId column to look into DegreeRequirements table. I suppose it is not a good practice to use such a column like entryId.

What is the way to solve this architecture issue?

Hristo Georgiev
  • 2,499
  • 1
  • 16
  • 23
StefanL19
  • 1,476
  • 2
  • 14
  • 29
  • If for every entry you create in RegisterEmployeeRequirements table you also create an entry in CreditStatusRequirement or EmployeeDegreeRequirement then you should have a foreing key relations ship CreditStatusRequirement -> RegisterEmployeeRequirements and CreditStatusRequirement -> RegisterEmployeeRequirements i.e. you should remove EntryId column from RegisterEmployeeRequirements table and make Id column in RegisterEmployeeRequirements as foreign key in CreditStatusRequirement and EmployeeDegreeRequirement tables. – The Shooter Aug 23 '16 at 09:09
  • 1
    See http://stackoverflow.com/questions/1654071/db-design-to-use-sub-type-or-not/1654483#1654483 ; http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 – Damir Sudarevic Aug 23 '16 at 09:19
  • Clarify cardinalities, `Employees` to `CreditStatusRequirement` and `Employees` to `EmployeeDegreeRequirement` – Serg Aug 23 '16 at 09:20
  • You need to outline what all three reference tables look like. You also need to tell us what you gain by storing the refernce in one column, rather than three columns. – Walter Mitty Aug 23 '16 at 10:06
  • 1
    Are table1 table2 and table3 subclasses of some common superclass? – Walter Mitty Aug 23 '16 at 10:07
  • No, they are not subclasses. But I consider implementing this hierarchy. – StefanL19 Aug 23 '16 at 10:38
  • I agree with others: we need more details on what the situation is (and most importantly what the constraints are). E.g.: are CreditStatusRequirement and EmployeeDegreeRequirement mutually exclusive? Can one employee have more than one requirement (of the same type or of different types?) – p.marino Aug 23 '16 at 13:24
  • I used table inheritance to solve this issue. It turned out to be useful in my case. I have an EmployeeRequirement as a supertype and CreditStatusRequirment and DegreeRequirement as subtypes. – StefanL19 Aug 24 '16 at 07:09
  • Do your logical data modelling before you start working with actual tables ;) – Juha K Aug 26 '16 at 07:18

2 Answers2

1

It's fairly simple. Don't have the FK equivalent in the RegisterEmployeeRequirements table. Have the FKs in each of the Credit and Degree requirements details tables to RegisterEmployeeRequirements.

Like this:

create table RegisterEmployeeRequirements(
  EmployeeId      int references ( ID ),
  RequirementType char( 1 ) not null,
  ..., -- Other common fields
  constraint PK_RegisterEmployeeRequirements primary key( EmployeeID, RequirementType ),
  constraint FK_RegisterEmployeeRequirements_Empe( EmployeeId )
    references Employees( ID ),
  constraint FK_RegisterEmployeeRequirements_Type( RequirementType )
    references RequirementTypes( ID ),
);

Notice the key is the combination of employee id and requirement id. This ensures each employee can have no more than one of each of the two defined requirements. I assume that is in line with your database requirements.

Then each requirements detail table can be defined something like this:

create table CreditRequirements(
  EmployeeId int primary key,
  RequirementType char( 1 ) check( CreditType = 'C' ),
  Status     ...,
  Acquired   datetime,
  constraint FK_CreditRequirements_Emp foreign key( EmployeeID, RequirementType )
    references RegisterEmployeeRequirements( EmployeeID, RequirementType )
);

create table DegreeRequirements(
  EmployeeId int primary key,
  RequirementType char( 1 ) check( DegreeType = 'D' ),
  DegreeName varchar( 64 ),
  MinGPA     float,
  constraint FK_DegreeRequirements_Emp foreign key( EmployeeID, RequirementType )
    references RegisterEmployeeRequirements( EmployeeID, RequirementType )
);

An entry in the credit details table can only be made for an employee that has a credit type entry made in the RegisterEmployeeRequirements table. Same for the degree details table for a degree type entry in RegisterEmployeeRequirements. No more than one of each type of requirement can be inserted in RegisterEmployeeRequirements and only one entry for each employee can be inserted in each of the details table.

Your data integrity is sound and the design is scalable. If a third requirement type is created, the type entry is inserted in the RequirementTypes table and a new details table is created for that type. None of the existing tables would need altering.

TommCatt
  • 5,498
  • 1
  • 13
  • 20
0

Why not just use separate tables for credit status and degree requirements, and remove the need for a RequirementType column?

table RegisterEmployeeCreditStatusRequirements has: Id, EmployerId, CreditStatus, DateTimeAcquired

table RegisterEmployeeEmployeeDegreeRequirements has: Id,EmployerId,DegreeName,MinGpa

David Scarlett
  • 3,171
  • 2
  • 12
  • 28