I want to know should I remove
Persons table or is it a good idea to have it in there?
In addition to Rob Farley's answer, a common pattern to guarantee that
PersonTypeId is correct, is to introduce a superkey in
ALTER TABLE Persons ADD CONSTRAINT ... UNIQUE (PersonTypeId, Id); ALTER TABLE Persons ADD CONSTRAINT ... CHECK ( PersonTypeId in ('C','D','S'));
and extend each of the sub-types with the type attribute. Now Customers can reference this super-key:
CREATE TABLE Customers ( CustomerID ... NOT NULL -- is this needed? , PersonId ... NOT NULL , PersonTypeId CHAR(1) DEFAULT 'C' NOT NULL , ... , CHECK ( PersonTypeId = 'C' ) , FOREIGN KEY ( PersonTypeId, PersonId) REFERENCES Persons (PersonTypeId, Id) );
A couple of DBMS (Sybase?) allow
SELECT in CHECK constraints. It is then not necessary to add the attribute in the sub-tables. Most DBMS don't allow this though, so it is fairly common to do like above.