Problem with database design. Column value pointing to different tables

Question

Description of what I should do

I have tableone that should be associated withTable1 OR Table2 OR Table3

For example, there is a table Employeesand it has: Id, Name, Address, Age, Salary, EmployerId

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

Where the type of requirements may be CreditStatusRequirementor EmployeeDegreeRequirement).

Problem : CreditStatusRequirementincludes both the dateCreditStatus and date when it was purchased (to check if it was last year). I also have an extra table with a name CreditStatusRequirementswith columns:

CreditStatus DateTimeAcquired

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

, . RegisterEmployeeRequirements CreditStatusRequirement, entryId, CreditStatusRequirements, , .

, EmployeeDegreeRequirement, entryId, DegreeRequirements. , , entryId, .

?

Answer 1

. FK RegisterEmployeeRequirements. FK RegisterEmployeeRequirements.

:

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 ),
);

, . , . , .

:

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 )
);

, , RegisterEmployeeRequirements. RegisterEmployeeRequirements. RegisterEmployeeRequirements, ​​ .

, . , RequirementTypes, . .

Answer 2

RequirementType?

RegisterEmployeeCreditStatusRequirements : Id, EmployerId, CreditStatus, DateTimeAcquired

table RegisterEmployeeEmployeeDegreeRequirements : Id, EmployerId, DegreeName, MinGpa