Primary Key Question

I'm planning to keep track of many [types of people], including patients,
doctors, and referrals. Additional types will likely be added in the future
(possibly "employees" or "staff")
I plan to have a People table in which common attributes are stored, and
then separate "type-specific" tables for each specific type of person... so
a table for Patients, another for Doctors, and another for Referrals.
What should I use for the primary key for the People table.
The hospital assigns a unique PatientID for each patient, and each doctor
has an EmployeeID. Those would be obvious keys for a Patient table and a
Doctor table. But in my situation the Patient and Doctor tables will have a
foreign-key relationship to the People table. So I can't use either
PatientID or EmployeeID for the People table.
What to do?
Thanks!What happens when an employee becomes a patient? Do you simply
inherit and populate both extensions? And if so, how do you explain
the Doctor-Patient relationship when you have a Doctor who is the
Patient of another Doctor?
I'm not trying to pick apart your design, but I think you'd be better
off keeping your entites of Doctor and Patient seperate, rather than
attempting to emulate object-oriented inheritance into a relational
design.
Stu|||To summarize: this database has multiple "types of people" and any given
person could be multiple types, although not usually (e.g,. a doctor could
be a patient, referrals must be tracked and some eventually become a
patient). For each "type of person" I could have a corresponding table.
Alternatively I could have a single Person table with some "flag" column
that tells us what kind of person we're dealing with; then a bunch of nulls
per row that are not relevant to any particular "person type". That is
perhaps the ugliest of all possible designs. Finally I could have one Person
table with all/only common attributes, then separate "person type-specific
tables" (e.g,. Employees, Doctors, Patients). This last design is apparently
most recommended (at least given feedback to my other post yesterday at 9:50
PM; subject = "1, 2, or 3 tables..."); and it's also the design I'm strongly
tending to favor (although I haven't finalized my decision).
This is really a question about tradeoffs between the alternative designs.
Each has issues to be mitigated, one of which is choice of primary key. So
for the [favored] design with one Person table and multiple associated
"person type-specific" tables, what do we do for the Primary key? Is this a
case for IDENTITY (or some surrogate?), with of course UNIQUE indexes on the
candidate natural key columns?
I hope someone can provide useful feedback for that specific question -
which is the purpose of this followup/new post.
Thanks!
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1140234808.668442.306920@.g47g2000cwa.googlegroups.com...
> What happens when an employee becomes a patient? Do you simply
> inherit and populate both extensions? And if so, how do you explain
> the Doctor-Patient relationship when you have a Doctor who is the
> Patient of another Doctor?
> I'm not trying to pick apart your design, but I think you'd be better
> off keeping your entites of Doctor and Patient seperate, rather than
> attempting to emulate object-oriented inheritance into a relational
> design.
>
> Stu
>|||I would be opposed to mixing patient data with any other data. The
rules governing confidentiality of patient data are extremely strict.
In the USA they are controlled by federal laws that carry severe
penalties. Likewise, rules on employee data are strict, but will be
different. Mixing them opens up too many possibilities for problems.
Roy|||Good points. However I'm reluctant to drive a design from factors that are
external to the domain being modeled.
That being said, even if I were to have separate tables for each "person
type" (and thereby satisfy the various "anti-mixing laws"), then I would be
introducing duplicate data when Doctor. John Smith becomes a Patient - which
is a perfectly legitimate scenario. So, you see it's all about tradeoffs; as
previously stated every possible design will ential certain problems to be
mitigated. I'm currently of the mind that I'd rather solve the problems of
having a "tighter" data model (i.e., no duplicate data by design), like
coming up with a primary key for the People table. Any ideas on that primary
key?
-Jeff
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ukadv1hgptp5kjkhnav8fstc3mp588o0l6@.
4ax.com...
>I would be opposed to mixing patient data with any other data. The
> rules governing confidentiality of patient data are extremely strict.
> In the USA they are controlled by federal laws that carry severe
> penalties. Likewise, rules on employee data are strict, but will be
> different. Mixing them opens up too many possibilities for problems.
> Roy|||Use a surrogate for People. Then make it the primary key for all three
tables. PatientID and EmployeeID can be alternate keys.
"Jeff S" <A@.B.COM> wrote in message
news:e4S92YCNGHA.1424@.TK2MSFTNGP12.phx.gbl...
> I'm planning to keep track of many [types of people], including patients,
> doctors, and referrals. Additional types will likely be added in the
> future (possibly "employees" or "staff")
> I plan to have a People table in which common attributes are stored, and
> then separate "type-specific" tables for each specific type of person...
> so a table for Patients, another for Doctors, and another for Referrals.
> What should I use for the primary key for the People table.
> The hospital assigns a unique PatientID for each patient, and each doctor
> has an EmployeeID. Those would be obvious keys for a Patient table and a
> Doctor table. But in my situation the Patient and Doctor tables will have
> a foreign-key relationship to the People table. So I can't use either
> PatientID or EmployeeID for the People table.
> What to do?
> Thanks!
>|||I should have probably been more clear in my post. The natural candidate
key for People may require not only the name, but also the address and maybe
even the date of birth in order to be unique, and it would be ridiculous to
include all of those attributes in the relation schema of every type of
person; therefore it makes sense to add a surrogate on People. The addition
of a surrogate on People doesn't eliminate the need for a unique constraint
on the natural candidate key, but it eliminates the need to duplicate the
attributes in the relation schema for every type of person. The value of a
surrogate is logically equivalent to the value of the natural candidate key
for which it is a surrogate. The surrogate becomes both the primary key and
a foreign key to People for every relation schema that is a direct subtype
of People. You could also have subtypes of subtypes, for example, Staff and
Doctors could be subtypes of Employees; in which case instead of referencing
People directly, Staff and Doctors would reference Employees, and Employees
would reference People. Note that the same surrogate is used for all types
of People, so if a given person is a Staff member, then the tuples in
People, Employee and Staff would have the exact same surrogate value.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:e3TCseHNGHA.3556@.TK2MSFTNGP10.phx.gbl...
> Use a surrogate for People. Then make it the primary key for all three
> tables. PatientID and EmployeeID can be alternate keys.
> "Jeff S" <A@.B.COM> wrote in message
> news:e4S92YCNGHA.1424@.TK2MSFTNGP12.phx.gbl...
>|||On Fri, 17 Feb 2006 21:37:45 -0800, "Jeff S" <A@.B.COM> wrote:

>Good points. However I'm reluctant to drive a design from factors that are
>external to the domain being modeled.
I would have said that the factors I mentioned are inherent to the
domain being modeled, but there are always several ways to look at
things.
Good luck!
Roy|||Thanks so much Brian for the explanation. I am of the same mind, but always
like to have additional perspective.
Now regarding the surrogate, this is a topic for which I have done much
research over the years (yes, CELKO will laugh at me or try to kill me...
not sure which)... but I can say that I like integers as small "fast" values
for the surrogate. But I also dislike IDENTITY for all the well-known
problems it entails. So for this database I was thinking of implementing
some scheme to generate unique integers to use for this "surrogate" primary
key. I am clear on some of the issues for going this route, but I think that
given this particular database some of those issues wouldn't be a big
concern - like having my "custom unique integer generator" deal with high
transaction volumes. The reason that particular issue is not of concern is
because the various "people" tracked by this database will be added
relatively slowly... only 10-50 new people per w.
So, what are some reasonable strategies for generating my own unique
integers? I've heard of "numbers tables" - what do you think?
-Jeff
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uDTDq3HNGHA.2636@.TK2MSFTNGP14.phx.gbl...
>I should have probably been more clear in my post. The natural candidate
>key for People may require not only the name, but also the address and
>maybe even the date of birth in order to be unique, and it would be
>ridiculous to include all of those attributes in the relation schema of
>every type of person; therefore it makes sense to add a surrogate on
>People. The addition of a surrogate on People doesn't eliminate the need
>for a unique constraint on the natural candidate key, but it eliminates the
>need to duplicate the attributes in the relation schema for every type of
>person. The value of a surrogate is logically equivalent to the value of
>the natural candidate key for which it is a surrogate. The surrogate
>becomes both the primary key and a foreign key to People for every relation
>schema that is a direct subtype of People. You could also have subtypes of
>subtypes, for example, Staff and Doctors could be subtypes of Employees; in
>which case instead of referencing People directly, Staff and Doctors would
>reference Employees, and Employees would reference People. Note that the
>same surrogate is used for all types of People, so if a given person is a
>Staff member, then the tuples in People, Employee and Staff would have the
>exact same surrogate value.
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:e3TCseHNGHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||What do I think? Why reinvent the wheel? IDENTITY works great if used
correctly. The problems with it are well known and as such can be easily
dealt with. The main problems with the other mechanisms are locking,
blocking and deadlocks, which are definitely more difficult to test, to
troubleshoot, and to eliminate (or at least to minimize).
Over the years I've seen several strategies. One used a number table, and
to minimize blocking, filled rows so that each occupied more than half of a
physical page. (This was before SQL 7.0, when page locks were the default,
but if an update in a transaction involves many tables, it's possible though
not very likely for row locks to escalate to page locks.) The main problem
with this solution is that you have to make sure that the lock on the number
table always occurs first in every procedure, trigger, etc., otherwise you
get deadlocks. Another solution uses SELECT MAX, but again, it also has
it's problems. You must use WITH(UPDLOCK, HOLDLOCK) to serialize access to
the table for any insert, and every process that may possibly insert one or
more rows must use the exact same mechanism.
The other issue--and you'll need to deal with this for the referencing
tables--is that surrogates must be immutable. This requires that either the
system prevent updates to the surrogate (as is the case with IDENTITY) or
that an update trigger (preferably an INSTEAD OF UPDATE trigger) exist on
each table that rolls back if an attempt to change the surrogate is
detected. When persisting object state, the relationship between a subtype
and a type is an "IS A" relationship, not a "HAS A" relationship, so it
doesn't make sense to allow the primary key to change, even though it is
also a foreign key.
"Jeff S" <A@.B.COM> wrote in message
news:%23wbZ%23oKNGHA.208@.tk2msftngp13.phx.gbl...
> Thanks so much Brian for the explanation. I am of the same mind, but
> always like to have additional perspective.
> Now regarding the surrogate, this is a topic for which I have done much
> research over the years (yes, CELKO will laugh at me or try to kill me...
> not sure which)... but I can say that I like integers as small "fast"
> values for the surrogate. But I also dislike IDENTITY for all the
> well-known problems it entails. So for this database I was thinking of
> implementing some scheme to generate unique integers to use for this
> "surrogate" primary key. I am clear on some of the issues for going this
> route, but I think that given this particular database some of those
> issues wouldn't be a big concern - like having my "custom unique integer
> generator" deal with high transaction volumes. The reason that particular
> issue is not of concern is because the various "people" tracked by this
> database will be added relatively slowly... only 10-50 new people per
> w.
> So, what are some reasonable strategies for generating my own unique
> integers? I've heard of "numbers tables" - what do you think?
> -Jeff
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uDTDq3HNGHA.2636@.TK2MSFTNGP14.phx.gbl...
>