How do I apply subtypes into an SQL Server databas

Question

I am working on a program in which you can register complaints. There are three types of complaints: internal (errors from employees), external (errors from another company) and supplier (errors made by a supplier). They hold different data which cannot be shared. I currently have 4 tables (complaint, employee, company and supplier). Here's a visualisation of the tables:

I have a basic understanding of subtypes but I cannot seem to translate them from an ERD into an actual SQL Server database, or at least in this scenario. This is roughly how the 4 tables look (irrelevant attributes omitted):

Complaint
ComplaintId PK

Employee
EmployeeId PK
EmployeeName

Company
CompanyId PK
CompanyName

Supplier
SupplierId PK
SupplierName

When registering a complaint, the error is made by either of the 3 types and they all store different information. What is the best way to store information in this case? I have thought of putting 2 discriminators in the Complaint-table: ComplaintType and Id so I can point to which table to check and what Id I need, but that isn't very clean nor efficient.

Please assist.

Answer 1

Is the main issue that you need some sort of "serial number" to uniquely identify a complaint regardless of which type? Basically, then, you need a table for each type of complaint (as you will have, I think), plus the master "Complaint" table with the ComplaintId. Each of the type-specific tables will have a foreign key to Complaint.ComplaintId. You may find it useful to have a "type" field in Complaint, but that isn't really required for the model.

Answer 2

See a few really good resources on the topic:

• Implementing table inheritance in SQL Server
• How to model inheritance in a database
• How do you effectively model inheritance in a database?

There's basically three well-known approaches:

• Table per Subclass
• Table per Hierarchy
• Table per Concrete Type

Each has pros and cons, shines in some situation and sucks in others - study the resources and see which of the three suits your needs the best.

Answer 3

You can have a complaintSubTypeID with a FK relationship to the PK of all three of your subtype tables- employee, company, and supplier.

Answer 4

I highly recommend you DO NOT use the "2 discriminators" method. You will effectively have a foreign key column that points to one of three tables, depending on the ComplaintType field. If you do this you will be by-passing the referential integrity checks supplied by SQL Server and all of the benefits that come with foreign keys. At my previous job, there was a table called EntityTypeIndexLabel which was a "bridge table" that attached IndexLabels (basically metadata) to various "entities", which were many different potential tables (Document, Binder, Workflow, etc...). This was simply awful. The FK in this table could point to many different tables. Orphaned records could pop-up everywhere. Extra logic had to be implemented to determine which table to join on. Joins were a pain to write in general. It was all kinds of headache.

I think your two options are:

-3 columns in Complaint: EmployeeComplaintID, CompanyComplaintID, SupplierComplaintID. ComplaintIDs should be unique across all of the tables (think GUIDs here instead of IDENTITY columns). Each row in Complaint will have only one of these IDs populated, the other two will be NULL. Then you can simply LEFT OUTER JOIN on these tables in every query to get the data that you need.

-One giant table with all of the possible fields you need for each complaint type, setting unused fields of other complaint types to NULL.

Answer 5

In response to you're comment on the accepted answer:

Below is a way to have a check check to ensure only one of the three keys has data:

alter table complaint_master 
    add constraint loc_attribute_has_one_value 
    check ( 
        (case when complaint_employee is null then 0 else 1 end) + 
        (case when complaint_supplier is null then 0 else 1 end) + 
        (case when complaint_external is null then 0 else 1 end)  = 1 
    );