Data Modeling Question; Patients and Referrals

A medical office for which I'm designing a database receives many many
referrals from other doctors offices. A small subset of these referrals
become patients. These referals and patients must be represented in the new
database.
The data collected for referrals is minimal, while the data for patients is
extensive. The data collected for Referrals is a subset of the data
collected for patients.
One solution is to have one Patients table with a "Status" column that
indicates whether the person is a Referral or Patient. This scenario would
result in a bunch of nulls, and would make it difficult/impossible to impose
column level constraints for Patients but not Referrals.
But it seems that we really have two entities here - Referrals and Patients.
And given that (1) the amount of data and (2) the constraints imposed (e.g.,
not null) are very different; it might make sense to have two tables.
Two questions:
1. Which modeling solution makes more sense? One table or two? (or someting
else?)
2. For the two-table solution described above: What would we do when a
Referral becomes a Patient. Do we add the referral info to the Patients
table and delete it from the Referrals table (i.e., move their data between
tables)?
Thanks!Jordan S. wrote:
> A medical office for which I'm designing a database receives many many
> referrals from other doctors offices. A small subset of these referrals
> become patients. These referals and patients must be represented in the ne
w
> database.
> The data collected for referrals is minimal, while the data for patients i
s
> extensive. The data collected for Referrals is a subset of the data
> collected for patients.
> One solution is to have one Patients table with a "Status" column that
> indicates whether the person is a Referral or Patient. This scenario would
> result in a bunch of nulls, and would make it difficult/impossible to impo
se
> column level constraints for Patients but not Referrals.
> But it seems that we really have two entities here - Referrals and Patient
s.
> And given that (1) the amount of data and (2) the constraints imposed (e.g
.,
> not null) are very different; it might make sense to have two tables.
> Two questions:
> 1. Which modeling solution makes more sense? One table or two? (or sometin
g
> else?)
> 2. For the two-table solution described above: What would we do when a
> Referral becomes a Patient. Do we add the referral info to the Patients
> table and delete it from the Referrals table (i.e., move their data betwee
n
> tables)?
> Thanks!
1. Two.
2. No. Each non-key attribute should appear in one place only - either
in the Patients table or the Referrals table but not both. Only the key
needs to be in both tables.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Referral becomes a Patient. Do we add the referral info to the Patients
> table and delete it from the Referrals table (i.e., move their data betwee
n
> tables)?
That would depend on what the business needs. Do you need to store the
original referral exactly as it came? For instance, suppose Jane Doe
gets a referral when she is 2 days shy of her 18th birthday, still
technically a minor, and gets to your office a w later, when she is
already an adult?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1150489639.272044.160730@.p79g2000cwp.googlegroups.com...
> Jordan S. wrote:
> 1. Two.
> 2. No. Each non-key attribute should appear in one place only - either
> in the Patients table or the Referrals table but not both. Only the key
> needs to be in both tables.
>
I agree that these should be separate. And the Patient table should have a
foreign key reference to the Referral table.
However, a Patient and a Referral can and probably should have "redundant"
data. It's patently absurd to store a Patient's name only on her related
Referral. A Patient has a Name, a Referral has a Name, but they are not
necessarily the same. And there may be Patients without Referrals. The
entities are related through business processes. This is a loose kind of
entity relationsihip, not an "is a" relationship.
This pattern happens a lot, where one entity becomes a different entity
through a business process process: a Proposal becomes a Contract, a Lead
becomes a Sale, or an Order becomes a Shipment. In general, when the new
entity is created it should have attributes copied from the old entity, and
a reference back to the old entity.
David|||David Browne wrote:
> It's patently absurd to store a Patient's name only on her related
> Referral.
Maybe the name of the table seems inappropriate somehow. That's easily
fixed. The idea of recording a patient's name only once isn't absurd at
all.

> A Patient has a Name, a Referral has a Name, but they are not
> necessarily the same.
Possibly. But then they are *different* attributes (the same person
entity can have both a Patient Name and a Referral Name). If that were
so then I would agree that they should appear in both tables. My
original advice still stands in that case because we are now talking
about *different* attributes.

> And there may be Patients without Referrals. The
> entities are related through business processes. This is a loose kind of
> entity relationsihip, not an "is a" relationship.
That's not an argument for redundancy though. In that case I would
create a third table for the common attributes.

> This pattern happens a lot, where one entity becomes a different entity
> through a business process process: a Proposal becomes a Contract, a Lead
> becomes a Sale, or an Order becomes a Shipment. In general, when the new
> entity is created it should have attributes copied from the old entity, an
d
> a reference back to the old entity.
I'd argue very strongly against "in general". What you have described
creates redundancy and the potential for anomaly. If an attribute is
the *same* attribute of the *same* instance of some entity then it
should generally be recorded exactly ONCE in the database. That's a
pretty important guiding principle of design. See Date and McGovern for
example:
http://www.dbdebunk.com/page/page/622331.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||RE:
<< This is a loose kind of entity relationsihip, not an "is a"
relationship.>>
Exactly. It's really a "was a" relationship (i.e., the Patient was-a
Referral).
But at the end of the day, it's still the same Person we're tracking (first
as a Referral, then [possibly] as a Patient. Our current design does, in
fact, extract out the common attributes to a 3rd "People" table... so
redundancy is [for now, anyway] eliminated.
-J|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1150493353.841502.230840@.i40g2000cwc.googlegroups.com...
> David Browne wrote:
> Maybe the name of the table seems inappropriate somehow. That's easily
> fixed. The idea of recording a patient's name only once isn't absurd at
> all.
>
> Possibly. But then they are *different* attributes (the same person
> entity can have both a Patient Name and a Referral Name). If that were
> so then I would agree that they should appear in both tables. My
> original advice still stands in that case because we are now talking
> about *different* attributes.
>
> That's not an argument for redundancy though. In that case I would
> create a third table for the common attributes.
>
> I'd argue very strongly against "in general". What you have described
> creates redundancy and the potential for anomaly. If an attribute is
> the *same* attribute of the *same* instance of some entity then it
> should generally be recorded exactly ONCE in the database. That's a
> pretty important guiding principle of design. See Date and McGovern for
> example:
I agree. It all turns on what is the same entity. As with normalization,
the possibility of update anomalies should be your guide. Back to the
doctor's office. If Patent and Refferal both have a PhoneNumber, and
someone updates the Patient.PhoneNumber is that an update anomaly? Perhaps.
If it is, then they should be modeled as one entity. Very possibly,
however, it is not an update anomaly, as it might make perfect sense for a
Referral stay unchanged. In that case, the Referral really models a
different entity. It represents the transaction with a referring doctor,
instead of an interaction with a Patient.
David|||A similar situation: there is a table Driver, and another table
Traffic_Accident_Party.
When Jane Doe gets a license, a row is inserted into Driver. When she
crashes her car, the information about the accident needs to be a
snapshot: when later Jane Doe marries, the traffic accident report
should still print with her maiden name. But I don't think her last
name needs to be copied to Traffic_Accident_Party. Instead, I would
store old versions of all the names, as well as the dates they were
valid from and to.
Traffic_Accident_Party should be as short as Driver_ID and
Traffic_Accident_ID.
Makes sense?