|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Data Modeling Question; Patients and Referralsreferrals 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:
Show quote > A medical office for which I'm designing a database receives many many 1. Two.> 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! 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 --
Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message I agree that these should be separate. And the Patient table should have a news:1150489639.272044.160730@p79g2000cwp.googlegroups.com... > 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 >> 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! > > 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. > 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:
> Maybe the name of the table seems inappropriate somehow. That's easily> It's patently absurd to store a Patient's name only on her related > Referral. 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 Possibly. But then they are *different* attributes (the same person> necessarily the same. 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 That's not an argument for redundancy though. In that case I would> entities are related through business processes. This is a loose kind of > entity relationsihip, not an "is a" relationship. create a third table for the common attributes. > This pattern happens a lot, where one entity becomes a different entity I'd argue very strongly against "in general". What you have described> 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. 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 --
Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message I agree. It all turns on what is the same entity. As with normalization, news:1150493353.841502.230840@i40g2000cwc.googlegroups.com... > 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, >> and >> 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: 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? 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 > Referral becomes a Patient. Do we add the referral info to the Patients That would depend on what the business needs. Do you need to store the> table and delete it from the Referrals table (i.e., move their data between > tables)? 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 week later, when she is already an adult?  |
|||||||||||||||||||||||
Other interesting topics