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.
-JThis 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|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... 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?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> 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
with the jokes since you started it out
with "Bambi" "). Then you have the best of both scenarios.
> 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)
"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
>|||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.|||>> 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!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> 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/datastandar...
.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)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> 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/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||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
end, but will try on Monday.