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.
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.