|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Primary Key Questiondoctors, 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! Show quote "Stu" <stuart.ainswo***@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 Show quote "Roy Harvey" <roy_har***@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 On Fri, 17 Feb 2006 21:37:45 -0800, "Jeff S" <A@B.COM> wrote: I would have said that the factors I mentioned are inherent to the>Good points. However I'm reluctant to drive a design from factors that are >external to the domain being modeled. domain being modeled, but there are always several ways to look at things. Good luck! Roy Thank you Roy!
I'll need the good luck : ) This is very much an art, what we do, designing databases. -Jeff Show quote "Roy Harvey" <roy_har***@snet.net> wrote in message news:6p2ev1l6748bvqm34tet5rn5rpbjpgs0d8@4ax.com... > 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 Although I can understand your desire to tighten your model, I don't
understand what drives that desire. What business goal do you accomplish by mimicing inheritance in this fashion? Are you ever going to report on the the number of people in your system? (BTW, I didn't see the discussion on your other post; I'll look at it after I put these thoughts down). I can understand an Employees table and a Patients table (and then having 0-1 relationships with extended attributes; an Employee is a doctor if s/he has certain credentials, etc), but in this case, vertical mapping (as you are investigating now) creates some problems: 1. The simple primary key issue (the one you're asking about): vertical mapping assumes that each of your people has a primary key that extends across each of your sub-entities. That may be the case, but identifying a simple primary key for a person is difficult (which is why the US relies so heaviliy on the SS# as a form of national id). If you horizontally partition, this issue is minimized because you can use a simple key that has meaning outside of your application (the employee ID#, the patient ID #). 2. The business rules (as Roy pointed out) differ for each of your subclasses. While you can restrict access to your database, you may have to jump through some hoops to make sure that People who are Patients have minimal exposure to other applications. 3. Vertical partioning increases complexity when retrieving information while reducing code redundancy. You make it easy on your developers to instantiate a patient object, but you're forcing your server to perform an additional join to retrieve that information. If this were a car dealership, and you were developing a database to hold information about Cars, Trucks, & SUV's, then vertical partioning makes more sense. The business of a car dealership is to sell Vehicles (the base table); a hospital doesn't serve people, it serves patients, who have relationships with doctors, staff, and referrals. Just to be facetious, what's keeping you from having a Primate table? If this is a research hospital, you could then include all the research animals in this database as well. I agree that database design is an art form, and choices we make as designers can have both intented and unintended consequences; I just think that the design you're proposing creates some issues that would be minimized using an alternate design. My intent here is NOT to do an object-oriented design. My complete
agenda is simply to come up with a relational database design that makes the most sense for the domain being modeled - then mitigate that design's implementation issues. About you being facetious - this IS a research hospital and I may need to add a primates table (no kidding; it would be an "animals" table). What's wrong with that? It's part of the "real world" as far as this client is concerned. In fact this hospital transplants animal organs into human beings - so the Donor could be (has been) an animal. Strange, this world we live in... Separately I'm not proposing this design, per se. Rather I presented it in the prior post as merely one of 3 possible designs for which I was requesting a recommendation from the NG. The general consensus was to go with this particular design. Funny how I got that consensus and then now that I'm going with it you guys are jumping on it as basically a bad idea fraught with problems. RE: << I just think that the design you're proposing creates some issues that would be minimized using an alternate design. >> That could be said about ANY design... "geeze man if you go with another design you won't have these problems". That's why we have so many designs - each entails specific tradeoffs (that's also why we have so many religions, but that's another story). In any case the purpose of this thread - if we can try to stay on topic - is to help the poor OP on mitigating some of the issues with the chosen/desired/recommended design. I could have chosen a different design (perhaps one you'd like) and then asked for assistance in mitigating ITS issues. [Switching to another design] isn't a reasonable recommendation because we'd switch and switch and switch, and then switch yet again because we'll never find the perfect design that has zero issues. -Jeff *** Sent via Developersdex http://www.developersdex.com *** Jeff S wrote:
> My intent here is NOT to do an object-oriented design. My complete OK, I may have misread your intent; this is just a common scenario> agenda is simply to come up with a relational database design that makes > the most sense for the domain being modeled - then mitigate that > design's implementation issues. > faced by many database developers when dealing with object-oriented programmers; both methodologies have their strngths and weaknesses, but they work best when isolated from each other. It's very tempting to make a database match the application design, but that may not be the best solution. Sorry if I read too much into it. > About you being facetious - this IS a research hospital and I may need I guess my joke wasn't clear enough; IF you were using object-oriented> to add a primates table (no kidding; it would be an "animals" table). > What's wrong with that? It's part of the "real world" as far as this > client is concerned. In fact this hospital transplants animal organs > into human beings - so the Donor could be (has been) an animal. Strange, > this world we live in... > methodologies to design your database, then I was curious as to why you didn't extend the inheritance chain to inherit People from a Primates table. Even now, why are differentiating People from Animals? > Separately I'm not proposing this design, per se. Rather I presented it As I said before, I missed your earlier post; I would have posted there> in the prior post as merely one of 3 possible designs for which I was > requesting a recommendation from the NG. The general consensus was to go > with this particular design. Funny how I got that consensus and then now > that I'm going with it you guys are jumping on it as basically a bad > idea fraught with problems. > as well, but it seemed inappropraite since this topic is now "HOT", and that one is colder. To be honest. though, the scenario you presented there could have been interpreted diffierently, as you were attempting to find common attrbutes between Customers and Employees. As this post has developed, other questions are being raised, such as: Are you ever going to report on the number of people in your system? Does the business domain care about People or does it care about specific PeopleTypes? > RE: Granted, that's true; but have you identified what those trade-offs> << I just think that the design you're proposing creates some issues > that would be minimized using an alternate design. >> > > That could be said about ANY design... "geeze man if you go with another > design you won't have these problems". That's why we have so many > designs - each entails specific tradeoffs (that's also why we have so > many religions, but that's another story). > would be? In your earlier post, you had three choices, and the one you're asking this question about has a problem dealing with simple primary keys; what are the problems with the other two? At this point, I'm not really advocating one approach over the other; I'm just asking if you've really considered all of the issues yet. > In any case the purpose of this thread - if we can try to stay on topic You're right; pick a design and stick with it. But pick the best> - is to help the poor OP on mitigating some of the issues with the > chosen/desired/recommended design. I could have chosen a different > design (perhaps one you'd like) and then asked for assistance in > mitigating ITS issues. [Switching to another design] isn't a reasonable > recommendation because we'd switch and switch and switch, and then > switch yet again because we'll never find the perfect design that has > zero issues. > > -Jeff > design, not just the one that's the easiest for you to code. I'm sorry if you're getting frustrated with the direction this thread is going, but asking a question in this newgroup will get varied responses. It's also true that there is no such thing as a perfect design; but there are some designs that are better than others (according to varied criteria). Use a surrogate for People. Then make it the primary key for all three
tables. PatientID and EmployeeID can be alternate keys. Show quote "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. Show quote "Brian Selzer" <br***@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... >> 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! >> > > 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 week. So, what are some reasonable strategies for generating my own unique integers? I've heard of "numbers tables" - what do you think? -Jeff Show quote "Brian Selzer" <br***@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" <br***@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... >>> 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 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. Show quote "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 > week. > > 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" <br***@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" <br***@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... >>>> 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! >>>> >>> >>> >> >> > > On Sat, 18 Feb 2006 08:42:07 -0800, "Jeff S" <A@B.COM> wrote: 99% of the databases I see these days use IDENTITY without a thought.>So, what are some reasonable strategies for generating my own unique >integers? I've heard of "numbers tables" - what do you think? You seem to have a small-scale app, you're not going to overflow it, don't see you mentioning any fancy replication issues, so why not just take the easy way out? Worry about "real" primary keys, 3NF normalization, and other indexes, but you don't seem to have any problem here! J. HI,
I know this is a bit late, But in similar designs I just go with a identity column. With a common peoples table, and then different child tables, eg doctor, patien, employee, etc, with foreign keys. Taht way, no matter what the hospital assigns as some key, your related tables would always depend on unchanging identity keys. know there is much discussion about identity, but the jury is still out on that one. Robert Show quote "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! > >  |
|||||||||||||||||||||||
Other interesting topics