Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2006
    Posts
    43

    Discriminator fields

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you have your FKs in the wrong entity

    do you have a diagram for those 4 entities?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    43
    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"??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vk101
    why would the Person table need to be the following?
    it wouldn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    43
    According to The Data Modeling Handbook: A Best-Practice Approach to Building Quality Data Models by Reingruber and Gregory, it would.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  7. #7
    Join Date
    Mar 2006
    Posts
    43
    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)!?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2006
    Posts
    43
    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...

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by vk101
    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 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:
    Code:
    CREATE TABLE Person 
    ( PersonId ... PRIMARY KEY, Disc ...
    , UNIQUE(PersonId, Disc)
    );
    CREATE TABLE Client
    ( PersonId ... PRIMARY KEY, Disc ...
    , FOREIGN KEY(PersonId,Disc) REFERENCES Person(PersonId,Disc)
    , CHECK (Disc='Client')
    );
    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

  11. #11
    Join Date
    Mar 2006
    Posts
    43
    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?

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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)
    Last edited by andrewst; 08-07-06 at 17:30.

  13. #13
    Join Date
    Mar 2006
    Posts
    43
    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??)

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  15. #15
    Join Date
    Mar 2006
    Posts
    43
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •