sql - Maintaining subclass integrity in a relational database

Question

Let's say I have a table that represents a super class, students. And then I have N tables that represent subclasses of that object (athletes, musicians, etc). How can I express a constraint such that a student must be modeled in one (not more, not less) subclass?

Clarifications regarding comments:

• This is being maintained manually, not through an ORM package.
• The project this relates to sits atop SQL Server (but it would be nice to see a generic solution)
• This may not have been the best example. There are a couple scenarios we can consider regarding subclassing, and I just happened to invent this student/athlete example.

A) In true object-oriented fashion, it's possible that the superclass can exist by itself and need not be modeled in any subclasses.

B) In real life, any object or student can have multiple roles.

C) The particular scenario I was trying to illustrate was requiring that every object be implemented in exactly one subclass. Think of the superclass as an abstract implementation, or just commonalities factored out of otherwise disparate object classes/instances.

Thanks to all for your input, especially Bill.

Answer 1

Each Student record will have a SubClass column (assume for the sake of argument it's a CHAR(1)). {A = Athlete, M=musician...}

Now create your Athlete and Musician tables. They should also have a SubClass column, but there should be a check constraint hard-coding the value for the type of table they represent. For example, you should put a default of 'A' and a CHECK constraint of 'A' for the SubClass column on the Athlete table.

Link your Musician and Athlete tables to the Student table using a COMPOSITE foreign key of StudentID AND Subclass. And you're done! Go enjoy a nice cup of coffee.

CREATE TABLE Student (
    StudentID INT NOT NULL IDENTITY PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Name VARCHAR(200) NOT NULL,
    CONSTRAINT UQ_Student UNIQUE (StudentID, SubClass)
);

CREATE TABLE Athlete (
    StudentID INT NOT NULL PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Sport VARCHAR(200) NOT NULL,
    CONSTRAINT CHK_Jock CHECK (SubClass = 'A'),
    CONSTRAINT FK_Student_Athlete FOREIGN KEY (StudentID, Subclass) REFERENCES Student(StudentID, Subclass)
);

CREATE TABLE Musician (
    StudentID INT NOT NULL PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Instrument VARCHAR(200) NOT NULL,
    CONSTRAINT CHK_Band_Nerd CHECK (SubClass = 'M'),
    CONSTRAINT FK_Student_Musician FOREIGN KEY (StudentID, Subclass) REFERENCES Student(StudentID, Subclass)
);