Subtypes

In this section I present the basics of how to translate subtypes into relations. I then describe how the optional versus mandatory and disjoint versus overlapping distinctions change these relations.

Subtypes

Consider again the company entity type and the two subtypes, AR_co and AP_co.

e14.png

For each of these entity types we want to store similar information:

company
id, name, address
AR_co
id, name, address, balance owed
AP_co
id, name, address, balance we owe

Consider a set of information we need to store:

  • 3, Von Maur, Briarwood, 300 we owe
  • 4, Verizon, Ann Arbor, 25 we owe
  • 6, UM, Ann Arbor, 400 owed to us

One way to store this information would be as follows:

AP_co
id name address owed by us
3 Von Maur Briarwood 300
4 Verizon Ann Arbor 25
AR_co
id name address owed to us
6 UM Ann Arbor 400

And, since we want to indicate that these are subtypes of company, we store the following information as well:

Company
id name address
3 Von Maur Briarwood
4 Verizon Ann Arbor
6 UM Ann Arbor

The trouble with this set of tables is that it does not take advantage of the fact that company stores the same information as the other two tables.

The proper table structures to use for this information is as follows:

company(__id__, name, address)
AP_co(__id__*, balanced_owed)
AR_co(__id__*, balance_we_owe)

The SQL create statements for these relations are as follows:

create table company
  (id type primary key,
   name type,
   address type);
create table AP_co
  (id type primary key,
   balance_owed type,
   foreign key (id) references company);
create table AR_co
  (id type primary key,
   balance_we_owe type,
   foreign key (id) references company);

Using these relations, the following information is stored:

company
id name address
3 Von Maur Briarwood
4 Verizon Ann Arbor
6 UM Ann Arbor
ap_co
id owed
3 300
4 25
ar_co
id we owe
6 400

Verify for yourself that all needed information can be retrieved and that no excess information is stored.

Problem: Why not use the following table structures?

company(__id__, name, address)
ar_co(__id__, fk_comp*, balance_owed)
ap_co(__id__, fk_comp*, balanced_owed)

The answer is that there is no need to create a new id for ar_co and ap_co and then reference a foreign key to the company table. We already know that the company id is unique for all the companies. By taking a subset of these companies (e.g., ar_co), id is still unique over this subset.

Optional and mandatory subtypes

The SQL create statements for both of these are exactly the same as above. This constraint must be implemented within other types of SQL statements or in the application's logic.

Disjoint and overlapping subtypes

The SQL create statements for both of these are exactly the same as above. This constraint must be implemented within other types of SQL statements or in the application's logic.

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License