I'm thinking about how to represent a complex structure in a SQL Server database.
Consider an application that needs to store details of a family of objects, which share some attributes, but have many others not common. For example, a commercial insurance package may include liability, motor, property and indemnity cover within the same policy record.
It is trivial to implement this in C#, etc, as you can create a Policy with a collection of Sections, where Section is inherited as required for the various types of cover. However, relational databases don't seem to allow this easily.
I can see that there are two main choices:
Create a Policy table, then a Sections table, with all the fields required, for all possible variations, most of which would be null.
Create a Policy table and numerous Section tables, one for each kind of cover.
Both of these alternatives seem unsatisfactory, especially as it is necessary to write queries across all Sections, which would involve numerous joins, or numerous null-checks.
What is the best practice for this scenario?
inheritance relationship in ERD should be represented as One-To-One relationship or One or Zero-to-One relationship depending on the case.
Inheritance is an important feature of Generalization and Specialization. It allows lower-level entities to inherit the attributes of higher-level entities. For example, the attributes of a Person class such as name, age, and gender can be inherited by lower-level entities such as Student or Teacher.
Inheritance is a common modeling technique used in modern software development. In data modeling, you can use inheritance in the logical model creation process.
SQL object inheritance is based on a family tree of object types that forms a type hierarchy. The type hierarchy consists of a parent object type, called a supertype, and one or more levels of child object types, called subtypes, which are derived from the parent.
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview:
Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will have to be given a NULL
value on rows where these attributes do not apply. With this model, you would have one policies table, which would look something like this:
+------+---------------------+----------+----------------+------------------+ | id | date_issued | type | vehicle_reg_no | property_address | +------+---------------------+----------+----------------+------------------+ | 1 | 2010-08-20 12:00:00 | MOTOR | 01-A-04004 | NULL | | 2 | 2010-08-20 13:00:00 | MOTOR | 02-B-01010 | NULL | | 3 | 2010-08-20 14:00:00 | PROPERTY | NULL | Oxford Street | | 4 | 2010-08-20 15:00:00 | MOTOR | 03-C-02020 | NULL | +------+---------------------+----------+----------------+------------------+ \------ COMMON FIELDS -------/ \----- SUBTYPE SPECIFIC FIELDS -----/
Keeping the design simple is a plus, but the main problems with this approach are the following:
When it comes to adding new subtypes, you would have to alter the table to accommodate the attributes that describe these new objects. This can quickly become problematic when you have many subtypes, or if you plan to add subtypes on a regular basis.
The database will not be able to enforce which attributes apply and which don't, since there is no metadata to define which attributes belong to which subtypes.
You also cannot enforce NOT NULL
on attributes of a subtype that should be mandatory. You would have to handle this in your application, which in general is not ideal.
Another approach to tackle inheritance is to create a new table for each subtype, repeating all the common attributes in each table. For example:
--// Table: policies_motor +------+---------------------+----------------+ | id | date_issued | vehicle_reg_no | +------+---------------------+----------------+ | 1 | 2010-08-20 12:00:00 | 01-A-04004 | | 2 | 2010-08-20 13:00:00 | 02-B-01010 | | 3 | 2010-08-20 15:00:00 | 03-C-02020 | +------+---------------------+----------------+ --// Table: policies_property +------+---------------------+------------------+ | id | date_issued | property_address | +------+---------------------+------------------+ | 1 | 2010-08-20 14:00:00 | Oxford Street | +------+---------------------+------------------+
This design will basically solve the problems identified for the single table method:
Mandatory attributes can now be enforced with NOT NULL
.
Adding a new subtype requires adding a new table instead of adding columns to an existing one.
There is also no risk that an inappropriate attribute is set for a particular subtype, such as the vehicle_reg_no
field for a property policy.
There is no need for the type
attribute as in the single table method. The type is now defined by the metadata: the table name.
However this model also comes with a few disadvantages:
The common attributes are mixed with the subtype specific attributes, and there is no easy way to identify them. The database will not know either.
When defining the tables, you would have to repeat the common attributes for each subtype table. That's definitely not DRY.
Searching for all the policies regardless of the subtype becomes difficult, and would require a bunch of UNION
s.
This is how you would have to query all the policies regardless of the type:
SELECT date_issued, other_common_fields, 'MOTOR' AS type FROM policies_motor UNION ALL SELECT date_issued, other_common_fields, 'PROPERTY' AS type FROM policies_property;
Note how adding new subtypes would require the above query to be modified with an additional UNION ALL
for each subtype. This can easily lead to bugs in your application if this operation is forgotten.
This is the solution that @David mentions in the other answer. You create a single table for your base class, which includes all the common attributes. Then you would create specific tables for each subtype, whose primary key also serves as a foreign key to the base table. Example:
CREATE TABLE policies ( policy_id int, date_issued datetime, -- // other common attributes ... ); CREATE TABLE policy_motor ( policy_id int, vehicle_reg_no varchar(20), -- // other attributes specific to motor insurance ... FOREIGN KEY (policy_id) REFERENCES policies (policy_id) ); CREATE TABLE policy_property ( policy_id int, property_address varchar(20), -- // other attributes specific to property insurance ... FOREIGN KEY (policy_id) REFERENCES policies (policy_id) );
This solution solves the problems identified in the other two designs:
Mandatory attributes can be enforced with NOT NULL
.
Adding a new subtype requires adding a new table instead of adding columns to an existing one.
No risk that an inappropriate attribute is set for a particular subtype.
No need for the type
attribute.
Now the common attributes are not mixed with the subtype specific attributes anymore.
We can stay DRY, finally. There is no need to repeat the common attributes for each subtype table when creating the tables.
Managing an auto incrementing id
for the policies becomes easier, because this can be handled by the base table, instead of each subtype table generating them independently.
Searching for all the policies regardless of the subtype now becomes very easy: No UNION
s needed - just a SELECT * FROM policies
.
I consider the class table approach as the most suitable in most situations.
The names of these three models come from Martin Fowler's book Patterns of Enterprise Application Architecture.
The 3rd option is to create a "Policy" table, then a "SectionsMain" table that stores all of the fields that are in common across the types of sections. Then create other tables for each type of section that only contain the fields that are not in common.
Deciding which is best depends mostly on how many fields you have and how you want to write your SQL. They would all work. If you have just a few fields then I would probably go with #1. With "lots" of fields I would lean towards #2 or #3.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With