Data Modeling Question: One Entity or Two

I am evaluating an existing MS Access database for a new client - with the
intent to migrate it to SQL Server and possibly change/improve the
relational database design in the process.
The client is a medical specialty office in which patients show up with lab
results taken by their primary care physician (PCP) prior to the patient
showing up at my client's specialty office. My client's office then proceeds
to take additional lab measurements over time. The data collected by the PCP
and my client's office is practically the same.
My client's existing MS Access database stores this information in two
tables - one for labs taken at the PCP office (and apparently only the most
recent set of results prior to the patient showing up at my client's
office); and another table for labs taken at my client's office (and
measured over time). In the existing database these two tables have similar
(almost identical) columns.
The client's in-house DBA sees these tables as representing two entities,
not one (one entity is "last set of labs measured by the PCP" and the other
entity is "labs measured in-house; over time"). I understand all these lab
results as one entity ("lab results"); and therefore we can/should move all
this data into one table when we migrate the data to the new SQL Server
database.
What do you think? Do these lab results represent one entity or two?
FWIW: The business managers do not differentiate between the two types of
labs (measured by PCP vs in-house).
Thanks!Sounds to me like one entity. I would add a column to one existing table
(possibly a CHAR(1)) that specified the source of the results. Be careful
in how you approach it though, as they might understand some arcane medical
regulation as specifying that they have to keep these data items physically
separated in some fashion (usually a misunderstanding of the regulations by
management, but I've seen stranger things...)
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||A good case for horizontal partitioning. With sql2k, I would keep them as 2
seperate entities (i.e 2 tables). But if you're on sql2k5, you might want to
look into table partitioning. There is definitely a performance gained by
partitioning them 'cuz the data for each will be smaller. Though, it would
require a union/join to look for data in both entities - a minor drawback in
this case.
-oj
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> [...] Do these lab results represent one entity or two?
I would use separate tables only if there is some information that
should be handled differently, for example if:
- the in-house lab results should be linked to a table about payments
(whereas the PCP resuls should not);
- the in-house lab results should have a MeasurerID column that refers
to our Employees table;
- etc.
Think about the such differences between the two types of lab results;
of course, if there are very few differences, they can be handled by
using a null-able column in the unified table (along some check
constraints, to enforce that for a particular type that column should
not be null). However, if there are more differences, having a lot of
null-able columns would not be "a Good Thing", so a two-tables approach
may be better in such a case.
Razvan|||If one table only has the most recent results, and the other tracks history,
you may be better off keeping them seperate. It could be a nightmare
cleaning up the "old" rows from the PCP if they are in the same table with
the history data. Also, if the foreign keys behave differently in these two
tables then you would want to keep them seperate.
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with
lab
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then
proceeds
> to take additional lab measurements over time. The data collected by the
PCP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
most
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
similar
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
other
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move
all
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with la
b
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then procee
ds
> to take additional lab measurements over time. The data collected by the P
CP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the mos
t
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have simila
r
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the othe
r
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move al
l
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
In principle if two potential entities have the same attributes then
they are only one entity.
In fact you can go further and say that entities that overlap (sharing
a common key and a common subset of non-key attributes) should be
represented as a single entity.
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
--|||Thanks for the feedback Jim. Can you please clarify "if the foreign keys
behave differently"?
What do you mean by "behave differently?
Thanks!
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
> If one table only has the most recent results, and the other tracks
> history,
> you may be better off keeping them seperate. It could be a nightmare
> cleaning up the "old" rows from the PCP if they are in the same table with
> the history data. Also, if the foreign keys behave differently in these
> two
> tables then you would want to keep them seperate.
> "Jeff" <A@.B.COM> wrote in message
> news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> lab
> proceeds
> PCP
> most
> similar
> other
> all
>|||If the two tables have different constraints, or foreign keys that point to
different tables, with different validation, then two tables is probably a
good idea.
If all of the columns in the tables share the same validation, against the
same tables, then one makes sense.
Here is one consideration, however...
If you did put all the data in one table, how do you enforce the business
rule that the PCP data have only one row and the internal data be allowed
history? Also, you need to discern between the two in the app. If you can
accomplish both of those (I'm sure there are many ways) then you should be
fine with one table.
OK, that was two considerations...
If I am being too vague I apologize. Without truly understanding the
business reason for keeping the data separate to begin with it is hard to
say exactly what should be done. From your own description one table seems
to make sense, but if their in house DBA were to explain it my opinion might
change.
"Jordan Richard" <A@.B.COM> wrote in message
news:eUY2mKqjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> Thanks for the feedback Jim. Can you please clarify "if the foreign keys
> behave differently"?
> What do you mean by "behave differently?
> Thanks!
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
with
patient
the
entities,
of
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O6Ve%238ujGHA.4660@.TK2MSFTNGP03.phx.gbl...
> If you did put all the data in one table, how do you enforce the business
> rule that the PCP data have only one row and the internal data be allowed
> history? Also, you need to discern between the two in the app. If you
> can
> accomplish both of those (I'm sure there are many ways) then you should be
> fine with one table.
You can address issue #1 via trigger. #2 can be addressed by adding a
column that specifies where the data came from (i.e., CHAR(1), 'L' = Local
source, 'P' = Partner source, etc.) Of course existing apps would have to
be modified.
Another consideration here might be how often you currently have to combine
the data from the two tables in a query. If you combine them often for
reporting or other purposes, combining them on the server makes sense. If
you don't ever combine them when querying, combining them might not be a
high priority.|||>> What do you think? Do these lab results represent one entity or two? ..
FWIW: The business managers do not differentiate between the two types of l
abs (measured by PCP vs in-house). <<
What is the LOGICAL difference between them? Apparently, none. Ergo,
you use one table and column for the lab_type. Leave this encoding
open enough that you can extend it lately, when add other sources.