Hi, I read a book that made it seem like discriminator fields should always be there in inheritance relationships...I just can't figure out why, though?

If you have entities Employee, Client, ServiceProvider, etc, you could have one entity Person as follows:

PersonID (PK)
EmployeeID (FK)
ClientID (FK)
ServiceProvider (FK)

But according to the book: "In data modeling applications, [multiple inheritance] is an illegal construct." ... Whoa, really!? Why? Seems like it works alright to me.

Although languages like Java don't permit multiple inheritance, it seems like this 'illegal' modeling strategy would be the only possible way to model interfaces (as per my other question).

So my questions are:
- is the sole purpose of discriminator fields to prohibit multiple inheritance?
- why is multiple inheritance not allowed?

Thanks.

i think you have your FKs in the wrong entity

do you have a diagram for those 4 entities?

You're right...it should be:

Person: PersonID (PK)
Employee: EmployeeID (PK), PersonID (AK, FK)
Client: ClientID (PK), PersonID (AK, FK)
ServiceProvider: ServiceProvider (PK), PersonID (AK, FK)

I'm not sure why something like this isn't considered good design...why would the Person table need to be the following?

Person: PersonID (PK), PersonDiscrimField (FK)
PersonDiscrim: DiscrimType (PK), where this field consists of "Employee", "Client", and "ServiceProvider"??

it wouldn't

According to The Data Modeling Handbook: A Best-Practice Approach to Building Quality Data Models by Reingruber and Gregory, it would.

The purpose of a discriminator column is to enforce distinct subtypes - i.e. a Person may be a Client or an Employee or a ServiceProvider, but not two or three of those things. If you do not have such a business requirement then you don't need a discriminator.

I see that if you have a discriminator field you can only put one item in it, such as "Client", "Employee", or "ServiceProvider", but I don't see how that enforces this requirement when inserting and searching:

INSERT INTO PersonTable (PK, Disc) VALUES ('1', 'Client');
INSERT INTO ClientTable (PK, PersonFK) VALUES ('100','1')

SELECT * FROM ClientTable c INNER JOIN PersonTable p ON c.PersonFK = p.PK WHERE p.Disc = 'Client'

Are the above statements correctly using the discriminator field?

I don't understand how the requirement is enforced though...couldn't someone accidentally enter a wrong value into the first INSERT? (i.e. 'Employee' instead of 'Client')

If we're relying on the person doing the INSERT to enter the discriminator correctly, why don't we rely on them to not have two subentities (e.g. Client, Employee) pointing to the same superentity (i.e. Person)!?

and while we're at it, what kind of modern business application restricts an employee from also being a client?

you know exactly what happens in that situation -- the users of the application will simply create a "new" person with exactly the same name, address, etc., and now there are duplicates in the database

yuck!!

In that particular case, you're right, multiple inheritance is more fitting. I should have pointed out that this is a theroretical example.

A place where single inheritance might be enforced is the classic Cat and Dog inheriting Animal...

If you wanted the database to enforce everything correctly you would need to define appropriate constraints. In full ANSI standard SQL you would be able to create a constraint something like:

CHECK ((Person.Disc='Client' AND EXISTS (SELECT NULL FROM Client WHERE Cleint.PersonFK = Person.PK) OR (Person.Disc!='Client' AND NOT EXISTS (SELECT NULL FROM Client WHERE Cleint.PersonFK = Person.PK))

and so on. But no DBMS I know of actually supports that, so you have to get around it like this:
The foreign key and check constraint on Client ensure that 'Client' persons can only have entries in the Client table. (However, they do not ensure that there is an entry in the Client table for every Person.)

Your inserts become:

INSERT INTO PersonTable (PK, Disc) VALUES ('1', 'Client');
INSERT INTO ClientTable (PK, PersonFK, Disc) VALUES ('100','1','Client');

Of course, you wouldn't expect an end-user to type all these in - your application would do it for them.

For the select, the following is sufficient:

SELECT * FROM ClientTable c INNER JOIN PersonTable p ON c.PersonFK = p.PK

Thanks for those examples.

Couple of questions on the CHECK constraint: It'd be on the Person table as opposed to the subentity table, right? And why isn't it supported...it doesn't seem like an unreasonable constraint?

Also, why is there a UNIQUE constraint on PersonID and Disc...PersonID is unique, so isn't the UNIQUE constraint redundant by adding in Disc?

It would probably belong on the Person table if it was a CHECK constraint. In another form, called an ASSERTION, it wouldn't belong to either table. I presume no vendor has implemented these yet because it is hard to do so, but I don't really know for sure. Some DBMSs support "materialized views", and with these you can mimic the effect of such a constraint.

The redundant UNIQUE constraint is there to allow the foreign key to reference both columns. That way we can ensure that Client.Disc = Person.Disc, while the CHECK constraint ensures that Child.Disc = 'Client'. So:

-- This fails due to check constraint:
INSERT INTO Person (PersonId, Disc) VALUES ('1', 'Employee');
INSERT INTO Client (PersonId, Disc) VALUES ('1','Employee');

-- This fails due to foreign key constraint:
INSERT INTO Person (PersonId, Disc) VALUES ('1', 'Employee');
INSERT INTO Client (PersonId, Disc) VALUES ('1','Client');

-- This fails due to either constraint:
INSERT INTO Person (PersonId, Disc) VALUES ('1', 'Client');
INSERT INTO Client (PersonId, Disc) VALUES ('1','Employee');

-- This succeeds:
INSERT INTO Person (PersonId, Disc) VALUES ('1', 'Client');
INSERT INTO Client (PersonId, Disc) VALUES ('1','Client');

(Note: there is no need for the Client table to have a separate primary key column - it's key is the same as that of Person)

Wow, that's cool, thanks!

Now there's nothing enforcing that an entry in the superentity table contains a matching entry in the appropriate subentity table...but that could be solved by placing a matching foreign key constraint in the Person table?

(If the above is correct, how are the two foreign key constraints obeyed upon inserting...one entry will be inserted before the other...in between both INSERTs, wouldn't one of the foreign key constraints be disobeyed??)

You couldn't create a foreign key from Person to Client, because not all Persons are Clients. Again, the multi-table CHECK constraint or ASSERTION would solve this if it actually existed! As things are, you would have to consider using database triggers, or perhaps the materialized view route (create a materialized view of Person LEFT OUTER JOIN Client and add a constraint that a Client-sourced column must be NOT NULL).

Regarding temporarily disobeyed constraints, most DBMSs do support this via "deferred constraint checking", where the constraints are not actually checked until you try to commit the whole transaction.

Aah, it's starting to come together.

Now if enforcing single inheritance isn't required (i.e. a Person can be a Client and Employee and ServiceProvider), I noticed that things might start to get very messy.

If you wanted to print out a list of all the categories to which a Person belongs, it'd be an ugly query. Assume the following tables:

Person: PersonID (PK)
Client: PersonID_FK (FK)
Employee: PersonID_FK (FK)
ServiceProvider: PersonID_FK (FK)

I imagine it'd be three separate select queries, joining each of the three subentities to Person? The mess would only start to get worse as more subentities are added to the hierarchy.

Is this the correct way of looking at this problem, or is there a better way to get the desired information in this multiple inheritance hierarchy?