How to Implement Total Disjoint Specialization in Database

How to implement total disjoint specialization in database?

I cannot figure out how to make a foreign key from two different tables.

You mean, a foreign key to/referencing two different tables. But there is no such foreign key in this design.

We declare an SQL FOREIGN KEY for a table to say that (ie to tell the DBMS that) the values for a list of columns are also values of a list of corresponding columns (maybe the same list) that are unique in a table (maybe the same table). You don't have this here. You have a different constraint on your tables.

If you want exactly those base tables then you have to use triggers in SQL to enforce your constraints.

You can also have a design with:

• base table Student with NOT NULL UNIQUE or PRIMARY KEY id
• FOREIGN KEYs from GradStudent (id), UndergradStudent (id) and Teaches (stud_id) REFERENCES Student (id)
• a constraint that the projection of Student on id is the disjoint union of the projections of GradStudent and UndergradStudent on id

You could express part the latter constraint by a trigger. A triggerless way to express the disjointedness (but not the union) is:

• a type discriminator/tag column student_type (say) in GradStudent, UndergradStudent & Student with additional FOREIGN (super) KEYs (id,student_type) from GradStudent and UndergradStudent to NOT NULL UNIQUE (id,student_type) in Student
• GradStudent CHECK( student_type = 'grad' ) and UndergradStudent CHECK ( student_type = 'undergrad' )

Rows in each of the two student subtype base tables are all the same (redundancy) and rows in Student are determined by their id (redundancy) but that's the cost in this case of having no triggers. Column student_type could be a computed column.

There's really no pretty SQL way to enforce that every parent id is a child. Having only the LEFT JOIN of the above child tables instead of the parent and child tables enforces that every parent is a child but requires NULL columns and further constraints. One needs triggers to reasonably constrain SQL databases. One uses idioms to get what declarative constraints one can.

For more on subtyping idioms see this answer and its links. Google 'stackoverflow database sql table' plus child/parent, super/subtables, super/subtypes, inheritance and/or polymorphism. Also multiple/many/two FKs/relationships/associations/references/links (although usually as in this question the constraint wanted is not a FK and the design should use subtypes instead). I googled "stackoverflow two foreign keys" and got this.

ORACLE SQL - Creating a disjoint specialisation

"Or is there a way without having seperate tables to store individual attributes."

There is no good way to avoid having separate tables. Bad ways include using a so-called generic data model (the entity-attribute-value pattern) or some form of quasi-structured storage such as JSON or XML.

These approaches are bad for several reasons.

1. It is very hard to understand the data model. The model is
   embedded how the data is stored rather than being expressed in
   tables and keys.
2. We cannot enforce keys and data integrity constraints on such
   models.
3. It is harder to write queries against such "models" because SQL is a strongly-typed language suited to static data models. The time saved by not doing proper data modelling is lost in writing the convoluted queries needed to handle the poor quality data (because of the previous point).

A robust solution would enforce the arc across the subtype tables with the full range of keys.

 create table items
   (item_id number not null primary key
    , item_type varchar2(10) not null 
    , item_name varchar2(30) not null
    , constraint item_uk unique (item_id, item_type)
    , constraint item_type_ck check (item_type in ('CLOTHES', 'COMPUTER'))
);

Two keys? Why two keys? So we can enforce the one-to-one relationship between parent and child records:

 create table clothes
   (item_id number not null primary key
    , item_type varchar2(10) not null 
    , size not null varchar2(5)
    , colour not null varchar2(5)
    , category not null varchar2(5)
    , constraint clothes_type_ck check (item_type = 'CLOTHES')
    , constraint clothes_item_fk foreign key (item_id, item_type)
        references items (item_id, item_type)
);

create table computers
   (item_id number not null primary key
    , item_type varchar2(10) not null 
    , ram_gb not null number
    , storage_gb not null number
    , os not null varchar2(10)
    , constraint computers_type_ck check (item_type = 'COMPUTER')
    , constraint computers_item_fk foreign key (item_id, item_type)
        references items (item_id, item_type)
);

The primary key on ITEMS ensures we only have one record for a given item_id. The unique key provides a reference point for the child tables, so we cannot have a COMPUTERS records and a CLOTHES record pointing to the same ITEMS record.

Difference between total specialization and disjoint rule in dbms?

When you use a total specialization, in the example shown, a patient must be either a outpatient or a resident patient, which means that all patients needs to be one of the sub types(outpatient or resident).

The disjoint rule is different in the way that a patient needs to be in only one subtype.

Basically, as I understand, the difference is that the total specialization says a super type needs to be in a sub type and the disjoint says it need to be in only one sub type.

I hope this helps.

There is a link that you can read about all these types and rules:
http://www.tomjewett.com/dbdesign/dbdesign.php?page=subclass.php

EER Diagram - Mixing Specialization and Disjointness Constraints Among Subtypes

Your first diagram is correct and clearly depicts the specialization you described.

Mixing overlapping and disjoint specialization in E-R model

Seems like this can only be enforced with a constraint. Thanks to everyone who gave some thought to this.

---

Related Topics