SQL - Prevent Polymorphic Association while still forcing a One to One Relationship

Question

Table CommercialBuildings
Columns:
- id
- company_owner
- number_offices
- (some other commercial specific stuff)

Table ResidentialBuildings
Columns:
- id
- are_pets_allowed
- has_garden
- (some other residential specific stuff)

I want to add a lot of information to both tables, e.g.:

- price
- square_meters
- address
- etc

Call this information "GenericBuildingInfo". I want to guarantee that each GenericBuildingInfo value set is just used ONCE in EITHER the commercial buildings or residential buildings.

Solution 1:

Table Buildings
Columns:
- id
- price
- square_meters
- adress
- (other generic information)
- commercial_building: ID from CommercialBuilding, can be NONE
- residential_building: ID from ResidentialBuilding, can be NONE

Constraint: EITHER commercial_building OR residential_building MUST be set.

This would create a table with nullable foreign keys, and many places dislike this solution, especially since this is not really fitting the relational model.

Some places instead propose "linking tables":

Solution 2:

Table GenericBuildingInfos
Columns:
- id
- price
- square_meters
- adress
- (other generic information*emphasized text*)

Table CommercialBuildingsGenericBuildingInfo
Columns:
- commerical_id: ID from CommercialBuildings
- generic_info_id: ID from GenericBuildingInfos

Table ResidentialBuildingsGenericBuildingInfo
Columns:
- residential_id: ID from ResidentialBuildings
- generic_info_id: ID from GenericBuildingInfos

Solution 2 would fit the relational concept. BUT in this solution it is not guaranteed that both the CommercialBuildingsGenericBuildingInfo AND the ResidentialBuildingsGenericBuildingInfo in some row reference the same generic_info_id. I want to be sure that a generic_info_id is just used once, so when it appears in the table CommercialBuildingsGenericBuildingInfo it does not appear in ResidentialBuildingsGenericBuildingInfo.

A straightforward way to solve the problem would be to copy the generic building information into both tables. So we could remove the "linking tables" and just expand the initial tables:

Solution 3:

Table CommercialBuildings
Columns:
- id
- company_owner
- number_offices
- (some other commercial specific stuff)
- price
- square_meters
- address

Table ResidentialBuildings
Columns:
- id
- are_pets_allowed
- has_garden
- (some other residential specific stuff)
- price
- square_meters
- address

Solution 3 guarantees that each generic building attribute (like the price) is directly mapped to either a residential building or a commercial building. BUT now I have copied the information to two different tables. However, the attribute is not specific to Residential or Commercial buildings; it is generic. If I add another generic attribute, such as has_solar_roof, then I need to add this in all tables that have the same kind of buildings.

What is a solution that fulfills the following requirements?

1. Style should be relational, e.g. not be dependent on additional constraints that are (Not fulfilled by Solution 1)
2. A set of Generic Info must only be referenced by one specific building (Not fulfilled by Solution 2)
3. The Generic Info must not be duplicated among multiple tables (Not fulfilled by Solution 3)

Answer 1

What you are describing is the common design pattern of super types and subtypes - if you Google for these you’ll find a lot of explanations and examples.

There are basically 3 ways of physicalising this design pattern, none are better or worse, you just need to decide which one is the best fit for your specific circumstances:

1. Keep all the attributes in a single super type entity
2. Push all the attributes down to subtype entities (and drop the supertype)
3. Keep the separate generic super type and specific subtype entries

Answer 2

I think #2 in relational inheritance is what you are looking for. #3 or #4 could also work for you in some cases. All three of them guarantee exclusivity between types.

Copying from the code:

create table document (
  id int primary key not null,
  title varchar(30),
  type char(1) not null check (type in ('I', 'B')),
  constraint uq unique (id, type)
);

create table invoice (
  id int primary key not null,
  amount decimal(12, 2) not null,
  type char(1) not null check (type = 'I'),
  foreign key (id, type) references document (id, type)
);

create table book (
  id int primary key not null,
  isbn varchar(13) not null,
  type char(1) not null check (type = 'B'),
  foreign key (id, type) references document (id, type)
);