SQL Apprentice Question
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
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).
>> 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). <<
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.