0

In a table per type architecture, say I have a type Person with two sub-types student and teacher. A student cannot be a teacher, and a teacher cannot be a student.

Below is the best way I know how to set these up so far: 

Geometry
  ID int, PK
  Name string

Point
  ID int, PK, FK
  X float
  Y float

Line
  ID int, PK, FK
  StartPoint int, FK
  EndPoint int, FK

Note that for the sub-types the primary key is also a foreign key to Geometry. How can I ensure that there is no Geometry that is both a Point and a Line? Is there some kind of mutually exclusive constraint?

If the only solution is to use something like triggers that are vendor-specific am I better of doing that or enforcing this somewhere else like the communication later or client?

thank you!

Eric
  • 1,392
  • 17
  • 37
  • So, you don't consider the idea that a person can't be both a teacher and a student to be a data integrity issue, but a logic issue? Isn't it redundant to have that enum? These are not rhetorical questions, I really don't know what's best – Eric Mar 28 '13 at 20:36
  • Maybe I'm thinking too abstractly, if this is high school or something clearly not, but any higher learning institution it certainly is possible. If they are so completely separate, why have them inherit from `Person` instead of just making 2 tables, `Teacher` and `Student`? – Jeremy Holovacs Mar 28 '13 at 20:39
  • I'm actually working on a very different database. In my database, the sub-types absolutely most be exclusive. I will edit the post to clarify – Eric Mar 28 '13 at 20:40
  • in that case, I would say there's not a lot of value in having both classes inherit from `Geometry`... without a compelling reason otherwise, I would make them completely separate objects with no common base and be done with it. – Jeremy Holovacs Mar 29 '13 at 03:00
  • My actual database has hundreds of tables with many situations where supertypes with exclusive sub-types are not only useful, but absolutely necessary. Since I cannot put my entire database model into a post, I came up with a simple example that demonstrates the architecture. – Eric Mar 29 '13 at 19:15

1 Answers1

0

There a few similar questions here on StackOverflow and on Database Administrators that might serve as a good reference point. See this related question:

How do I model one entity that references one of several other entities in SQL?

Community
  • 1
  • 1
Tim Lentine
  • 7,782
  • 5
  • 35
  • 40
  • 2
    This is more appropriate as a comment than an answer. – Gordon Linoff Mar 28 '13 at 20:40
  • It has definitely lead me in the right direction and I appreciate that :) I don't mind compiling my results into an answer and posting it once I've figured it out. – Eric Mar 28 '13 at 20:45
  • http://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes/4898314#4898314 this guy seems to know his stuff – Eric Mar 28 '13 at 20:47