| Groups | Blog | Home |
sql server programming : Data Modeling Question
I'm designing a database for a medical group that must keep track of various
"People" Some are doctors and some are patients. The client currently categorizes patients according to the type of procedure(s) they have been seen for (e..g, "Jane is a Botox patient because she had Botox injections" while "Ralph is a hair transplant patient because he's had hair transplants." And on and on it goes). These procedures are obviously not mutually exclusive given that any given patient can have more than one type of procedure. As I see the situation we have [Patients] and [Procedures]. We do NOT have [patient types] even though that's how the client understands them. We just have patients who have various procedures. My whiz bang plan is to simply have a many-to-many relationship between [Patients] and [Procedures]. This will work fine for identifying the so called "patient types"... just SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get "the Botox patients". Question 1: What would be a good way to classify a patient who has not yet had any procedure? Say Bambi comes in and gets scheduled for Botox. The doctors would want her to show up on reports as a "Botox patient" even though she hasn't yet had the procedure. Question 2: Given that [Doctors] and [Patients] are fundamentally different "things" in this database, is it reasonable to have two tables - one for Doctors and another for Patients... or is it recommended to have one table ("People") and then have some "PersonType" column that flags the person as a doctor or a patient (and then have a bunch of NULLS for columns not relevant to each row's designated "person type"). The one-table approach seems kind of ugly. Just wanted some feedback on this before I go off and implement. Thank you for your time and consideration. -J
This has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have). You may have an equivalent 'procedures' table. Procedures table would likely have budget/costs associated with the procedure. Another table would have the procedure history for a person. A person could have multiple records in that table, each would have a key for the person, the procedure name, the procedure key (for joining to 'procedures' table). This table would have a startdate and enddate for the procedure. A person who is scheduled, but has not yet had the procedure merely has a futuredated record in this table (based on startdate). When the procedure is done, you give the record an enddate. Doctors and patients all belong in the same table b/c a doctor could be a patient and vice versa. Each person has their own unique id and also a second field which is the id of that persons PCP. So if you have: name, uniqueid, PCPid dr smith, 1, 0 dr jones, 2, 0 sick guy,3,1 dr williams,4,2 This means that sick guy goes to dr. smith. Dr williams goes to Dr. Jones. You likely WILL need a flag field that lets you clearly determine who is a doc ('flagdoc' that has y/n or 1/0 for everyone). Based on similar relationships, this is how my company does it. Theoretically, maybe you don't have to put the procedure name in the procedure history, but it's nice having it there. HTH, wayne
[quoted text, click to view]
>> is it reasonable to have two tables - one for Doctors and another for Patients... or is it recommended to have one table ("People") << Doctors and patients are logically different, so I would scrape the idea of a general "Peoiple" table. Where is the "Treatments" table that would show the dates (scheduled, actual, etc.), location, doctor(s), etc. for Bambi's Botox? [quoted text, click to view] >> a good way to classify a patient who has not yet had any procedure? << As a patient. The assorted procedures done to them are events and not attributes of the patient himself.
wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice?? The futuredated record mentioned above could be a bit dangerous b/c people will definitely back out on things. Our place has a whole module for 'applicants'. If they are hired, then they get records for jobs, etc. The data model for people who say they 'want to do something in the future' could be pretty complex...
The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the above layout for employees and their dependents (which is a nice, static relationship). It does sound like the patients at your place can have numerous doctors work on them over the course of time. Your db revolves around the procedure--which can have one patient, one or two docs. Splitting out may well be the best way to go.
[quoted text, click to view]
> Question 1: What would be a good way to classify a patient who has not yet > had any procedure? Say Bambi comes in and gets scheduled for Botox. The > doctors would want her to show up on reports as a "Botox patient" even > though she hasn't yet had the procedure. I would suggest you document people throughout their lifecycle with you. So when the patient comes in, planning to get Botox, a row is created in the Patients and PatientProcedures table. Another table would be related to the patientProcedures table that would document the status of the relationship. Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and so on (I will assume you are cool with the jokes since you started it out with "Bambi" "). Then you have the best of both scenarios. [quoted text, click to view] > Question 2: Given that [Doctors] and [Patients] are fundamentally > different "things" in this database, is it reasonable to have two tables - > one for Doctors and another for Patients... or is it recommended to have > one table ("People") and then have some "PersonType" column that flags the > person as a doctor or a patient (and then have a bunch of NULLS for > columns not relevant to each row's designated "person type"). The > one-table approach seems kind of ugly. Just wanted some feedback on this > before I go off and implement. Tough call. I would would not suggest the one table approach, but a table for generic "people" attributes, and another for patient attributes. I wouldn't have a PersonType in this case because a person could be both (the key of the two subordinate tables would be the same as for the Person table so a person could only be mapped once.) The existance of a row in the patient table would indicate that the person is a patient. (Will you have nurses, sleep makers (can't spell anesthesiologist) and such. Particularly for billing and/or scheduling I would imagine.) Now you have everything you need (I think) you can tell the type of patient immediately, including their status "Planned" "Botox", "Scheduled" "Hair Transplant" and after > 1 procedures takes place: "Planned" "Repeat" "Botox". Then they can get specific about the types of patient that they are looking at. -- ---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) [quoted text, click to view] "Jordan R." <A@B.COM> wrote in message news:ux0URV4LGHA.3100@tk2msftngp13.phx.gbl... > I'm designing a database for a medical group that must keep track of > various "People" > > Some are doctors and some are patients. > > The client currently categorizes patients according to the type of > procedure(s) they have been seen for (e..g, "Jane is a Botox patient > because she had Botox injections" while "Ralph is a hair transplant > patient because he's had hair transplants." And on and on it goes). These > procedures are obviously not mutually exclusive given that any given > patient can have more than one type of procedure. > > As I see the situation we have [Patients] and [Procedures]. We do NOT have > [patient types] even though that's how the client understands them. We > just have patients who have various procedures. > > My whiz bang plan is to simply have a many-to-many relationship between > [Patients] and [Procedures]. > > This will work fine for identifying the so called "patient types"... just > SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get > "the Botox patients". > > Question 1: What would be a good way to classify a patient who has not yet > had any procedure? Say Bambi comes in and gets scheduled for Botox. The > doctors would want her to show up on reports as a "Botox patient" even > though she hasn't yet had the procedure. > > Question 2: Given that [Doctors] and [Patients] are fundamentally > different "things" in this database, is it reasonable to have two tables - > one for Doctors and another for Patients... or is it recommended to have > one table ("People") and then have some "PersonType" column that flags the > person as a doctor or a patient (and then have a bunch of NULLS for > columns not relevant to each row's designated "person type"). The > one-table approach seems kind of ugly. Just wanted some feedback on this > before I go off and implement. > > Thank you for your time and consideration. > > -J >
[quoted text, click to view]
>> wouldn't it be pretty common for a doctor to also be a patient of his/her own group practice? << No, not in the US; insurnace companies would go nuts. Can you say "FRAUD!!"? [quoted text, click to view] >> The future_dated record mentioned above could be a bit dangerous b/c people will definitely back out on thing << So we need both an actuial and schedule appointment date. Sounds like a good source for stats and predictions!
[quoted text, click to view] Louis Davidson wrote: > > is it recommended to have > > one table ("People") and then have some "PersonType" column that flags the > > person as a doctor or a patient > > Tough call. I would would not suggest the one table approach, but a table > for generic "people" attributes, and another for patient attributes. Which country? The generic "people" approach seems to be the one taken by the UK's National Health Service (the world's largest?) In the interest of standards, they have published their data dictionary: http://www.nhsia.nhs.uk/datastandards/pages/dd/data_dictionary/classes/p/person_at.asp?shownav=1 Jamie. --
Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a minimum First Name, Last Name, mailing address, etc, perhaps some form of Id Number, perhaps. The basics. Some of these things in their list would be very offensive to Americans. For some reason we will give up our "tax" number (social security number, which is becoming too much of a citizen id number) Then the patient would have a file number, perhaps if all records are stored in a paper format, medical information, etc. This table would be related to the appointment calendar, billing. Then doctors information, abilities, schedule, etc. By no means is this a required way to do it. Having two tables with some minor overlap of information is not horrible when the two concepts are going to have little interaction (for example, if you had to balance a doctor's appointment schedule as a patient AND a doctor, this would be essential.) Frankly if the only overlapping information in a medical system is that a doctor is entered as a patient of another doctor AND a doctor of patients, the world would rejoice at not having to explain why they want to see the doctor 10 times. -- ---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) [quoted text, click to view] "onedaywhen" <jamiecollins@xsmail.com> wrote in message news:1139819374.327665.281610@f14g2000cwb.googlegroups.com... > > Louis Davidson wrote: >> > is it recommended to have >> > one table ("People") and then have some "PersonType" column that flags >> > the >> > person as a doctor or a patient >> >> Tough call. I would would not suggest the one table approach, but a >> table >> for generic "people" attributes, and another for patient attributes. > > Which country? The generic "people" approach seems to be the one taken > by the UK's National Health Service (the world's largest?) In the > interest of standards, they have published their data dictionary: > > http://www.nhsia.nhs.uk/datastandards/pages/dd/data_dictionary/classes/p/person_at.asp?shownav=1 > > Jamie. > > -- >
[quoted text, click to view] Louis Davidson wrote: > Some of these things in their list would be very offensive to Americans. > For some reason we will give up our "tax" number (social security number, > which is becoming too much of a citizen id number) That's why I opened with, 'Which country?" :) If the OP (or other interested reader) is in the UK then choosing to follow the NHS model is one way of resolving the quandary. FWIW here we seem to be moving in the opposite direction e.g. identity cards for all :( Jamie --
[quoted text, click to view]
> identity cards for all :( Shudder...Just wait 'til they realize that identity cards are too easy to fake....You want to stick what? where? -- ---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) [quoted text, click to view] "onedaywhen" <jamiecollins@xsmail.com> wrote in message news:1139905293.909702.73940@g44g2000cwa.googlegroups.com... > > Louis Davidson wrote: >> Some of these things in their list would be very offensive to Americans. >> For some reason we will give up our "tax" number (social security number, >> which is becoming too much of a citizen id number) > > That's why I opened with, 'Which country?" :) If the OP (or other > interested reader) is in the UK then choosing to follow the NHS model > is one way of resolving the quandary. > > FWIW here we seem to be moving in the opposite direction e.g. identity > cards for all :( > > Jamie > > -- >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |