How do I apply subtypes into an SQL Server database?
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
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.