The third option is to create a "Policy" table and then a "SectionsMain" table to store all the fields that are common across different types of sections. Then create additional tables for each type of section, containing only the uncommon fields.

Deciding which one is best depends mainly on how many fields you have and how you want to write your SQL. They will all work. If you only have a few fields, then I'd probably go with #1. For "a lot" of areas, I'd lean towards #2 or #3.

@Bill KarwinIn hisSQL Antipatternsbook, he proposed the SQLEntity Attribute Valueanti-pattern. Here's a brief overview:

Single table inheritance (aka per-hierarchy table inheritance):

Using a single table like the first option is probably the simplest design. As you mentioned, many subtype-specific properties must be given NULL values on rows where these properties do not apply. Using this model, you would have a policy table that looks 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 an advantage, but the main problems with this approach are as follows:

• As you add new subtypes, you must change the table to accommodate the properties that describe these new objects. This can quickly become a problem when you have many subtypes or when you plan to add subtypes regularly.

• The database will not be able to enforce which properties apply and which do not, because there is no metadata to define which properties belong to which subtypes.

• You also cannot enforceNOT NULLon a subtype attribute that should be enforced. You have to handle this in the application, which is usually not ideal.

Specific table inheritance:

Another way to solve the inheritance problem is to create a new table for each subtype, repeating all the common properties 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 by the single-table approach:

• Mandatory attributes can now be enforced viaNOT NULL.

• Adding new subtypes requires adding a new table, not adding columns to an existing table.

• There is also no risk of setting inappropriate attributes for specific subtypes, such as thevehicle_reg_nofield of an attribute policy.

• No need for thetypeattribute like in the single table method. The type is now defined by metadata: table name.

But this model also has some shortcomings:

• Public properties are mixed with subtype-specific properties, and there is no easy way to identify them. The database doesn't know either.

• When defining a table, you must repeat the common properties for each subtype table. This is definitely not干.

• Searching for all strategies regardless of subtype becomes difficult and requires a bunch ofUNION.

Regardless of type, you must query all policies via:

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;

Please note that adding new subtypes will require modifying the above query with additionalUNION ALLfor each subtype. If you forget to do this, you can easily cause errors in your application.

Class table inheritance (aka table inheritance per type):

This is the solution mentioned by @David in another answer . You create a table for the base class that includes all public properties. You would then create specific tables for each subtype, whose primary keys also serve as base tables. 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 found in the other two designs:

• Mandatory attributes can be enforced byNOT NULL.

• Adding new subtypes requires adding a new table, not adding columns to an existing table.

• There is no risk of setting inappropriate properties for specific subtypes.

• Notypeattribute is required.

• Now public properties are no longer mixed with subtype specific properties.

• We can finally stay dry. Table creation does not require duplication of common properties for each subtype table.

• Managing auto-increment policies foridbecomes easier as this can be handled by the base table rather than each subtype table generating them independently.

• Searching all strategies (regardless of subtype) is now very easy: noUNIONrequired - justSELECT * FROM strategy.

I think the class table method is the most appropriate in most cases.

---

The names of these three models come fromMartin Fowlera bookEnterprise Application Architecture Patterns.