|
|
You're in the
sql server programming
group:Data Modeling Question: One Entity or Two
sql server programming:
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 [quoted text, click to view] "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! >
[quoted text, click to view]
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
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...) [quoted text, click to view] "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! >
[quoted text, click to view]
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 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! 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 --
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. [quoted text, click to view] "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! > >
Thanks for the feedback Jim. Can you please clarify "if the foreign keys
behave differently"? What do you mean by "behave differently? Thanks! [quoted text, click to view] "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... >> 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! >> >> > >
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. [quoted text, click to view] "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... > > 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! > >> > >> > > > > > >
[quoted text, click to view]
>> 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.
[quoted text, click to view]
"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.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |