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.
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:
|
|
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:
|
|
|
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.