SQL script for subtype table

Question

In SQL, I have a table FACILITIES (FacilityID PRIMARY KEY, FacilityName, FacilityAddress, FacilityPhone, FacilyType)

FacilityType refers to subtype tables of

1. AFFILIATION (FacilityID PK, AffiliationStartDate, AffiliationEndDate, StaffType, AffiliationVerified);

2. EMPLOYER (FacilityID PK, HireDate, TermDate, Position, HRContactName)

3. CLIENT (FacilityID PK, ContactName, ContactEmail, ConstractStart, ContractEnd, ContractActive)

As is, the FACILITY can be 1 or more of the subtypes - which is true. A facility can be both a client and an employer. How do I write a script tying the subtypes to FACILITY?

I was thinking of a junction table but can I do that with subtype tables?

Answer 1

Subtype (or child) tables - need their own PK and the Parent PK becomes a Foreign Key allowing you to associate one or more child records to the parent.

So your subtype table definitions become:

AFFILIATION (AffiliationID PK, FacilityID FK, AffiliationStartDate, AffiliationEndDate, StaffType, AffiliationVerified);

EMPLOYER (EmployerID PK, FacilityID FK, HireDate, TermDate, Position, HRContactName)

CLIENT (ClientID PK, FacilityID FK, ContactName, ContactEmail, ConstractStart, ContractEnd, ContractActive)

Then your query becomes

SELECT * FROM
FACILITY F
AFFILIATION A LEFT OUTER JOIN F.FacilityID = A.FacilityID
EMPLOYER E LEFT OUTER JOIN F.FacilityID = E.FacilityID
CLIENT A LEFT OUTER JOIN F.FacilityID = C.FacilityID