Suppose I have a database that includes 5 different "types" of people, and
each person could simultaneously be of multiple types (the types don't
really matter... so pretend it has doctors, lawyers, janitors, students, and
customers).
My Question: In general, is it perfectly acceptable in a scenario like this
to have one People table for common attributes and then 5 additional "person
type-specific" tables (e.g., a Doctors table, Lawyers, Janitors, Students,
and Customers tables) that each hold type-specific attributes?
Or is there a *general* recommendation to go with 5 tables total and
duplicate the common attributes amongst the tables.
Please understand my objective is to come up with a solid relational
database design - NOT simply to emulate the object-oriented programming
concept of inheritance in my database; and yes, I do understand the issues
related to choice of primary key (my plan is to use an IDENTITY property to
glue the People table with the various "person type" tables, and then have a
UNIQUE constraint on the "real" natural keys per "person type").
Thanks!"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>
Your first suggestion is better. The following article explains how and why
tables should not duplicate each other (more precisely, their predicates
should not overlap):
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
--|||Why do I find that amusing :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23CQjX2eUGHA.1564@.TK2MSFTNGP11.phx.gbl...
> http://www.dbdebunk.com|||Think about it this way and it might be clearer:
People are people and have attributes that describe them as a person (Name,
DOB, etc)
People can play different roles such as Doctor, Lawyer, etc
So, you would likely have a table named Person to store those attributes
that are about the person
Then, seperate tables for each role the person may play
"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>
Showing posts with label modeling. Show all posts
Showing posts with label modeling. Show all posts
Sunday, March 11, 2012
Data Modeling: Managing a group of events, both Special & Regular.
Scenario: The system needs to manage events by group. There is no hierachica
l
concept in the events. Special events have different attributes that need to
be treated differently. Note that the DeviceID has a many-to-one relationshi
p
with a LocationID (not in the model). Not seen in the model also include
PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
Eventually the system will need to report all the related events for a
special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID i
n
SpecialEvent, what would be the advantages/di
vantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, the drafted database design is as the
following:
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])Hi CTO,
1. I question the basic structure of the SpecialEvents Table. Are there
Multiple "Special Events" for any single Ordinary Event? If not then the
Special Event Table Primary Key should be the same as the Event Table Primar
y
Key, and it should be a one-to-one relationship, not a many-to-one
relationship. So, what's up with "TypeCode" in the SpecialEvents Table? If
there can be multiple Rows in this table witht he same SpecialEventID, but
with different "TypeCode" values, then this table, as structured, is NOT a
table of SpecialEvents, it's something else, (what I don't know) and should
be renamed, and a real specialEvents table should be added.
2. Please clarify what you mean by "Related Events". Do the groups of
Relate dEvents Overlap? i.e., could Event A be related to EventB and Event
B
be related to EventC, but EventC NOT be related to EventA? like for example
cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, even
though Bob and SAlly are unrelated... Or,
Are all the "Groups" formed by the relations non-overlapping distinct
groups?, as for eample Citys and States, or Football teams and Leagues, etc.
If it's the latter then the more appropriate way to "relate" the events, is
to just adda groupID in the events table, and make the value the same for
all the events in the group... If there's some data attribute that is
associated with the group, and not with teach individual event, then add an
EventGroup Table that just has GroupID (as PK) and those Group-specific Data
attributes as additional columns...
hth,
Charly
"C TO" wrote:
> Scenario: The system needs to manage events by group. There is no hierachi
cal
> concept in the events. Special events have different attributes that need
to
> be treated differently. Note that the DeviceID has a many-to-one relations
hip
> with a LocationID (not in the model). Not seen in the model also include
> PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
> Eventually the system will need to report all the related events for a
> special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3) Ple
ase
> comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID
in
> SpecialEvent, what would be the advantages/di
vantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, the drafted database design is as the
> following:
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
l
concept in the events. Special events have different attributes that need to
be treated differently. Note that the DeviceID has a many-to-one relationshi
p
with a LocationID (not in the model). Not seen in the model also include
PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
Eventually the system will need to report all the related events for a
special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID i
n
SpecialEvent, what would be the advantages/di
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, the drafted database design is as the
following:
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])Hi CTO,
1. I question the basic structure of the SpecialEvents Table. Are there
Multiple "Special Events" for any single Ordinary Event? If not then the
Special Event Table Primary Key should be the same as the Event Table Primar
y
Key, and it should be a one-to-one relationship, not a many-to-one
relationship. So, what's up with "TypeCode" in the SpecialEvents Table? If
there can be multiple Rows in this table witht he same SpecialEventID, but
with different "TypeCode" values, then this table, as structured, is NOT a
table of SpecialEvents, it's something else, (what I don't know) and should
be renamed, and a real specialEvents table should be added.
2. Please clarify what you mean by "Related Events". Do the groups of
Relate dEvents Overlap? i.e., could Event A be related to EventB and Event
B
be related to EventC, but EventC NOT be related to EventA? like for example
cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, even
though Bob and SAlly are unrelated... Or,
Are all the "Groups" formed by the relations non-overlapping distinct
groups?, as for eample Citys and States, or Football teams and Leagues, etc.
If it's the latter then the more appropriate way to "relate" the events, is
to just adda groupID in the events table, and make the value the same for
all the events in the group... If there's some data attribute that is
associated with the group, and not with teach individual event, then add an
EventGroup Table that just has GroupID (as PK) and those Group-specific Data
attributes as additional columns...
hth,
Charly
"C TO" wrote:
> Scenario: The system needs to manage events by group. There is no hierachi
cal
> concept in the events. Special events have different attributes that need
to
> be treated differently. Note that the DeviceID has a many-to-one relations
hip
> with a LocationID (not in the model). Not seen in the model also include
> PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
> Eventually the system will need to report all the related events for a
> special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3) Ple
ase
> comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID
in
> SpecialEvent, what would be the advantages/di
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, the drafted database design is as the
> following:
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
Data Modeling: Managing a group of events, both Special & Regu
A minor correction: StandardEventID should read SpecialEventID
Having removed the TypeCode from the SpecialEvent, and knowing that any
EventID can be in anyway related to either relatedEvent or SpecialEvent, is
it safe to model as the following?
1. All events initially is created in Event table.
2. If there is a special event, which has additional attributes, then a
special event will be created (triggered?). The same eventID will be
identified in the SpecialEvent table. No related event has been created at
this point.
3. Say, when there are more than 100 events, for example, and we start
relating/grouping them, we can get EventID from Event to RelatedEvent Table.
Since Event can have both ordinary and special eventIDs, both eventIDs can b
e
related in RelatedEvent. Before I continue, are there any issues so far? To
continue, with this approach, I can see one possible problem here, for
reporting, each time we need to a special event with standard/common event
attributes, we will need to join the Event table. Besides, to identify a
special event from the Event table, we must join SpecialEvent. However, ther
e
should be no more than 100 special events in a w
and the reporting period
usually does not span more than 13 w
s.
"CBretana" wrote:
> Hi CTO,
> 1. I question the basic structure of the SpecialEvents Table. Are there
> Multiple "Special Events" for any single Ordinary Event? If not then the
> Special Event Table Primary Key should be the same as the Event Table Prim
ary
> Key, and it should be a one-to-one relationship, not a many-to-one
> relationship. So, what's up with "TypeCode" in the SpecialEvents Table?
If
> there can be multiple Rows in this table witht he same SpecialEventID, but
> with different "TypeCode" values, then this table, as structured, is NOT a
> table of SpecialEvents, it's something else, (what I don't know) and shoul
d
> be renamed, and a real specialEvents table should be added.
> 2. Please clarify what you mean by "Related Events". Do the groups of
> Relate dEvents Overlap? i.e., could Event A be related to EventB and Even
t B
> be related to EventC, but EventC NOT be related to EventA? like for examp
le
> cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, eve
n
> though Bob and SAlly are unrelated... Or,
> Are all the "Groups" formed by the relations non-overlapping distinct
> groups?, as for eample Citys and States, or Football teams and Leagues, et
c.
> If it's the latter then the more appropriate way to "relate" the events, i
s
> to just adda groupID in the events table, and make the value the same for
> all the events in the group... If there's some data attribute that is
> associated with the group, and not with teach individual event, then add a
n
> EventGroup Table that just has GroupID (as PK) and those Group-specific Da
ta
> attributes as additional columns...
>
> hth,
> Charly
> "C TO" wrote:
>1. No, do not use a trigger... Are you using stored procs exclusively to
access the system? If so, write yr Insert Update Stored Proc(s) (I use one
for both, but you may have one each) to take a parameter to Identify whethe
r
Event is "Special", (Or, just include Special Event's attribuites, with null
default values, and only passs them when it IS a special Event.
Then, inside SP Logic, after creating or updating Event Table, since SP
'Knows" (based on parameter flag, or presence/absence of Special Attribute
data), whether it's dealing with a regular or special event, either
insert/update, or delete (if Special is changing back to regular) the row in
the SpecualEvent Table...
2. If event "relations can overlap, then a single Event can be in more than
one "group". The structure you had only allows a Chain of Associations A-B,
B-C, C-D, D-E, etc... It does not allow A,B,C,D to all belong equally to the
same group. WHich of these models is correct? If it's the latter, then you
need a separate table to hold these associations...
Create Table EventAssociations
(GroupID Integer Not Null,
EventID Integer Not Null,
Primary Key (GroupID, EventID)
)
If you need to store any data attributes about the group, then you need an
EventGroups Table as well.
Create Table EventGroups
(GroupID Integer Primary Key Not Null,
GroupName VarChar(30),
..
)
3. <snip>Since Event can have both ordinary and special eventIDs, both
eventIDs can be related in RelatedEvent...</snip> NO. For every Event, a
row should be created in Events Table, For Special Events, a row should be
created in Events Table AND in SpecialEvents Table, but use the same EventI
D
in Both tables for that row... WHy use a different ID ? Then you just have
to create a mapping from one to the other. This way the EventID of
SpecialEvents TAble can be used not only as PK, but as FK back to EventID in
Events Table. In Stored Proc Code, create Event Record, get ID of record
created, (If you're using IDentity, and then use that VAlue t oinsert
SpecialEVent Record PK. I'd even call the SpecialEVent table PK column NAme
the exact same (EventID), not SpecialEventID, just to make it clearthey ar
e
the same value.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w
and the reporting peri
od
> usually does not span more than 13 w
s.
> "CBretana" wrote:
>|||4. <snip>I can see one possible problem here, for reporting, each time we
need to a special event with standard/common event attributes, we will need
to join the Event table</snip> Yes, so what? The data is in the other
table... that's what relational databases are designed to do. The only
alternative is to put all the data into the Events Table. If you had
Employees Table, with 80k rows, and four extra really long data attributes
you needed to store for the 20 employees in the legal Department, would you
a) add those four attributes to all 80,000 employees,
b) Move 20 legal employees into a separate table that had the extra four
attributes,
(This means to get, or search, or Update against ALL EMployees you
have to UNion both tables together.),
c) Create a separate table for Just those four attributes, and leave
regular employee data in the main table.
ANSWER C.
Also, if determining whether an event is "Special" is a common task,
INDEPENDANT of accessing the Special Data, then add a flag to Events Table
to indicate whether the row is a Special Event... You could name it it
"IsSpecial" and type it as your favorite boolean type - (You can use Char(1)
with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0).
THis is a denormalization, (it is redundant, since presence of row with same
eventID in SpecialEvents table is same datum), but it will allow you to
determine "SpecialNess" without the Join.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w
and the reporting peri
od
> usually does not span more than 13 w
s.
> "CBretana" wrote:
>|||FoA, Many Thanks.
1. Agreed. When I use the word "trigger", I did not mean trigger in database
sense. I agree with you.
2. I am
with the concept of having a GroupID. the events do not
have a Chain of Association. For example 1-2, 1-4, 4-3,4-1,4-7, so when I
want to retrieve all events related to 4, I will yeild 1,3,4,7 in my view. I
t
is mainly for project management. Someone has to have the flexibility to
group the events the way they want for event management purpose. For example
,
say we have 5 TypeCodes in specialEvent, and:
a. if I want to create a new TypeCode of that EventID, I can create a new
EventID in Event and SpecialEvent with a that TypeCode.
b. Later, I can also relate an ordinary past EventID to this special
EventID. I can also add a new (future) ordinary EventID to relate to the sam
e
special EventID.
c. Eventually, the event/project manager needs to view all related events
for a particular events, to check if all the 5 TypeCodes have been taken
place, as well as to analyze all their related events.
There is no hierachical or sequencial concept when grouping. Is there a
better way to implement this scenario? Regardless, I am still interested in
the GroupID concept here, can you elaborate?
3. Yes, the specialEventID derives from EventID.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||Having said in #2c in my previous response, the fact I removed TypeCode from
the PK, I would have to create a different EventID for a related
specialEvent, which is preferable and good. It is prefereable because that i
s
consistent with the convention. It is good because another TypeCode event is
another event, not the same event. Because of that, however, if I want to be
able to track the changes in the event, for example, today two tasks have
been added for that event, and because they are for the same place, reason
(TypeCode), by the same people, I do not want to add new event, then I need
to have task table?
Create Table Task
(EventID Integer Not Null,
TaskID Integer Not Null,
StartDate DateTime Null,
EndDate DateTime Null
Primary Key (EventID, TaskID)
)
Good thing about doing this also includes SpecialEventHistory table may be
avoided. Bad thing, for example may be each time I need know the status of
the event with a specific TypeCode, I will need to, again, join this table.
How would you approch this?
Please note that I do not have specific requirements other than I mentioned.
What we design now should cover for most, if not almost all, possible
scenarios.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||When you "Add" A related event to a special Event, that insert should
initially be handled exactly as any other new event... It should go into
regular Events Table, and then, if it's ALSO a Special Event, it should also
go in the SpecialEvents Table. Then and only then woud it go into the
EventAssociations Table, which stores Relations between events.
The concept of GroupID, Name it something Else like "RelationID" i just a
way t oassociate all the events that are related t oone another, but allow
events to be related in overlapping groups...
Say you had the following
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Then Event 1 is related to Event 3, and Event 5 through one "relationship",
(Group 1)
and to Events 2 & 5 through another overlapping relationship...
If the relationships cannot overlap, then you would need a different
structure.
"C TO" wrote:
> Having said in #2c in my previous response, the fact I removed TypeCode fr
om
> the PK, I would have to create a different EventID for a related
> specialEvent, which is preferable and good. It is prefereable because that
is
> consistent with the convention. It is good because another TypeCode event
is
> another event, not the same event. Because of that, however, if I want to
be
> able to track the changes in the event, for example, today two tasks have
> been added for that event, and because they are for the same place, reason
> (TypeCode), by the same people, I do not want to add new event, then I nee
d
> to have task table?
> Create Table Task
> (EventID Integer Not Null,
> TaskID Integer Not Null,
> StartDate DateTime Null,
> EndDate DateTime Null
> Primary Key (EventID, TaskID)
>
> )
> Good thing about doing this also includes SpecialEventHistory table may be
> avoided. Bad thing, for example may be each time I need know the status of
> the event with a specific TypeCode, I will need to, again, join this table
.
> How would you approch this?
> Please note that I do not have specific requirements other than I mentione
d.
> What we design now should cover for most, if not almost all, possible
> scenarios.
> "CBretana" wrote:
>|||OK, let do a select illustration here:
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Select EventID from EventAssociation E
where Exists ( Select 1 from EventAssociation G
Where E.GroupID = G.GroupID and EventID = 2)
Returns:
EventID
--
1
2
5
7
To get the same result, without a groupID, I have to do the following:
EventID RelatedEventID
1 3
1 2
2 5
3 5
2 7
5 7
3 1 -- this insert may or may not happen
Select Case when EventID = 2 then RelatedEventID
when RelatedEventID = 2 then EventID
end as EventID
from EventAssociation G
Where 2 in (EventID, RelatedEventID)
What would be the punishment? Obviously, I must have not seen the benefits
of GroupID here.
"CBretana" wrote:
> When you "Add" A related event to a special Event, that insert should
> initially be handled exactly as any other new event... It should go into
> regular Events Table, and then, if it's ALSO a Special Event, it should al
so
> go in the SpecialEvents Table. Then and only then woud it go into the
> EventAssociations Table, which stores Relations between events.
> The concept of GroupID, Name it something Else like "RelationID" i just a
> way t oassociate all the events that are related t oone another, but allow
> events to be related in overlapping groups...
> Say you had the following
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Then Event 1 is related to Event 3, and Event 5 through one "relationship"
,
> (Group 1)
> and to Events 2 & 5 through another overlapping relationship...
> If the relationships cannot overlap, then you would need a different
> structure.
>
> "C TO" wrote:
>|||No, to get related Events with GroupID structure, you just
Select Distinct O.EventID
From EventAssociation E
Join EventAssociation O
On O.GroupID = E.GroupID
Where E.EventID = 2
This is "Cleaner" In that it is asking "Show me all the Events that are in
any of the the Groups Event 2 is in", and it doesn't imply any ordering or o
f
one event to the other... As your design structure does... (EventID,
RelatedEventID implies a directional, binary relationship, not a grouping o
f
2 or more events that are all equally related to one another .
So what I suggested more closely represents a Business model where multiple
events (2 or many) are related to one another with no distinction between
which one is "master" and which ones are "subordinate". Your structure woul
d
be better id business model for a "relationship" which IS directional (in
each pair there is a left/right, or master/subordinate, or etc.) and if the
relationships are all binary (pairs), and there is therefore a distinction
between a direct Relationship and an Indirect one...
EventID RelatedEventID
1 3
3 2
-- here Event 1 is Directly related to Event 3,
and Event 3 is directly related to Event 2,
but Event 1 is only indirectly related to Event 2
I don't know which business model more accurately represents your business,
and without that knowledge I can not tell yo which of these structure would
be more appropriate..
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Your structure would be appropriate for relationships among people in a
Table, where you want to model Teacher-Student Relationships. It is:
1. Binary,
2. Drectional, (Just because I am your teacher, doesn't mean you are my
teacher), If we were each other's Teacher, we would put two records in the
table, one for [me to you], and one for [you to me]...
3. And there are direct and indirect relationships... If Bob is Mary's
Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher to
Mary...
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Thank you. The relationship in this model is not binary, but it is true for
(2) and (3). I am learning toward using GroupID now but the only thing left
is that it does not provide indirect relationship...
Perhaps indirect relationship with GroupID can be achived this way:
From the prev example of EventID 2, to related any direct relationship with
EventID 2, we get 1,5,7. If we are looking for one level up, anything that
has a relationship to EventID 2's siblings (1,5,7), we would look through
Groups that EventID 2 are in, right?
"CBretana" wrote:
> Your structure would be appropriate for relationships among people in a
> Table, where you want to model Teacher-Student Relationships. It is:
> 1. Binary,
> 2. Drectional, (Just because I am your teacher, doesn't mean you are my
> teacher), If we were each other's Teacher, we would put two records in th
e
> table, one for [me to you], and one for [you to me]...
> 3. And there are direct and indirect relationships... If Bob is Mary's
> Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher
to
> Mary...
> "C TO" wrote:
>
Having removed the TypeCode from the SpecialEvent, and knowing that any
EventID can be in anyway related to either relatedEvent or SpecialEvent, is
it safe to model as the following?
1. All events initially is created in Event table.
2. If there is a special event, which has additional attributes, then a
special event will be created (triggered?). The same eventID will be
identified in the SpecialEvent table. No related event has been created at
this point.
3. Say, when there are more than 100 events, for example, and we start
relating/grouping them, we can get EventID from Event to RelatedEvent Table.
Since Event can have both ordinary and special eventIDs, both eventIDs can b
e
related in RelatedEvent. Before I continue, are there any issues so far? To
continue, with this approach, I can see one possible problem here, for
reporting, each time we need to a special event with standard/common event
attributes, we will need to join the Event table. Besides, to identify a
special event from the Event table, we must join SpecialEvent. However, ther
e
should be no more than 100 special events in a w
usually does not span more than 13 w
"CBretana" wrote:
> Hi CTO,
> 1. I question the basic structure of the SpecialEvents Table. Are there
> Multiple "Special Events" for any single Ordinary Event? If not then the
> Special Event Table Primary Key should be the same as the Event Table Prim
ary
> Key, and it should be a one-to-one relationship, not a many-to-one
> relationship. So, what's up with "TypeCode" in the SpecialEvents Table?
If
> there can be multiple Rows in this table witht he same SpecialEventID, but
> with different "TypeCode" values, then this table, as structured, is NOT a
> table of SpecialEvents, it's something else, (what I don't know) and shoul
d
> be renamed, and a real specialEvents table should be added.
> 2. Please clarify what you mean by "Related Events". Do the groups of
> Relate dEvents Overlap? i.e., could Event A be related to EventB and Even
t B
> be related to EventC, but EventC NOT be related to EventA? like for examp
le
> cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, eve
n
> though Bob and SAlly are unrelated... Or,
> Are all the "Groups" formed by the relations non-overlapping distinct
> groups?, as for eample Citys and States, or Football teams and Leagues, et
c.
> If it's the latter then the more appropriate way to "relate" the events, i
s
> to just adda groupID in the events table, and make the value the same for
> all the events in the group... If there's some data attribute that is
> associated with the group, and not with teach individual event, then add a
n
> EventGroup Table that just has GroupID (as PK) and those Group-specific Da
ta
> attributes as additional columns...
>
> hth,
> Charly
> "C TO" wrote:
>1. No, do not use a trigger... Are you using stored procs exclusively to
access the system? If so, write yr Insert Update Stored Proc(s) (I use one
for both, but you may have one each) to take a parameter to Identify whethe
r
Event is "Special", (Or, just include Special Event's attribuites, with null
default values, and only passs them when it IS a special Event.
Then, inside SP Logic, after creating or updating Event Table, since SP
'Knows" (based on parameter flag, or presence/absence of Special Attribute
data), whether it's dealing with a regular or special event, either
insert/update, or delete (if Special is changing back to regular) the row in
the SpecualEvent Table...
2. If event "relations can overlap, then a single Event can be in more than
one "group". The structure you had only allows a Chain of Associations A-B,
B-C, C-D, D-E, etc... It does not allow A,B,C,D to all belong equally to the
same group. WHich of these models is correct? If it's the latter, then you
need a separate table to hold these associations...
Create Table EventAssociations
(GroupID Integer Not Null,
EventID Integer Not Null,
Primary Key (GroupID, EventID)
)
If you need to store any data attributes about the group, then you need an
EventGroups Table as well.
Create Table EventGroups
(GroupID Integer Primary Key Not Null,
GroupName VarChar(30),
..
)
3. <snip>Since Event can have both ordinary and special eventIDs, both
eventIDs can be related in RelatedEvent...</snip> NO. For every Event, a
row should be created in Events Table, For Special Events, a row should be
created in Events Table AND in SpecialEvents Table, but use the same EventI
D
in Both tables for that row... WHy use a different ID ? Then you just have
to create a mapping from one to the other. This way the EventID of
SpecialEvents TAble can be used not only as PK, but as FK back to EventID in
Events Table. In Stored Proc Code, create Event Record, get ID of record
created, (If you're using IDentity, and then use that VAlue t oinsert
SpecialEVent Record PK. I'd even call the SpecialEVent table PK column NAme
the exact same (EventID), not SpecialEventID, just to make it clearthey ar
e
the same value.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w
od
> usually does not span more than 13 w
> "CBretana" wrote:
>|||4. <snip>I can see one possible problem here, for reporting, each time we
need to a special event with standard/common event attributes, we will need
to join the Event table</snip> Yes, so what? The data is in the other
table... that's what relational databases are designed to do. The only
alternative is to put all the data into the Events Table. If you had
Employees Table, with 80k rows, and four extra really long data attributes
you needed to store for the 20 employees in the legal Department, would you
a) add those four attributes to all 80,000 employees,
b) Move 20 legal employees into a separate table that had the extra four
attributes,
(This means to get, or search, or Update against ALL EMployees you
have to UNion both tables together.),
c) Create a separate table for Just those four attributes, and leave
regular employee data in the main table.
ANSWER C.
Also, if determining whether an event is "Special" is a common task,
INDEPENDANT of accessing the Special Data, then add a flag to Events Table
to indicate whether the row is a Special Event... You could name it it
"IsSpecial" and type it as your favorite boolean type - (You can use Char(1)
with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0).
THis is a denormalization, (it is redundant, since presence of row with same
eventID in SpecialEvents table is same datum), but it will allow you to
determine "SpecialNess" without the Join.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w
od
> usually does not span more than 13 w
> "CBretana" wrote:
>|||FoA, Many Thanks.
1. Agreed. When I use the word "trigger", I did not mean trigger in database
sense. I agree with you.
2. I am
have a Chain of Association. For example 1-2, 1-4, 4-3,4-1,4-7, so when I
want to retrieve all events related to 4, I will yeild 1,3,4,7 in my view. I
t
is mainly for project management. Someone has to have the flexibility to
group the events the way they want for event management purpose. For example
,
say we have 5 TypeCodes in specialEvent, and:
a. if I want to create a new TypeCode of that EventID, I can create a new
EventID in Event and SpecialEvent with a that TypeCode.
b. Later, I can also relate an ordinary past EventID to this special
EventID. I can also add a new (future) ordinary EventID to relate to the sam
e
special EventID.
c. Eventually, the event/project manager needs to view all related events
for a particular events, to check if all the 5 TypeCodes have been taken
place, as well as to analyze all their related events.
There is no hierachical or sequencial concept when grouping. Is there a
better way to implement this scenario? Regardless, I am still interested in
the GroupID concept here, can you elaborate?
3. Yes, the specialEventID derives from EventID.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||Having said in #2c in my previous response, the fact I removed TypeCode from
the PK, I would have to create a different EventID for a related
specialEvent, which is preferable and good. It is prefereable because that i
s
consistent with the convention. It is good because another TypeCode event is
another event, not the same event. Because of that, however, if I want to be
able to track the changes in the event, for example, today two tasks have
been added for that event, and because they are for the same place, reason
(TypeCode), by the same people, I do not want to add new event, then I need
to have task table?
Create Table Task
(EventID Integer Not Null,
TaskID Integer Not Null,
StartDate DateTime Null,
EndDate DateTime Null
Primary Key (EventID, TaskID)
)
Good thing about doing this also includes SpecialEventHistory table may be
avoided. Bad thing, for example may be each time I need know the status of
the event with a specific TypeCode, I will need to, again, join this table.
How would you approch this?
Please note that I do not have specific requirements other than I mentioned.
What we design now should cover for most, if not almost all, possible
scenarios.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||When you "Add" A related event to a special Event, that insert should
initially be handled exactly as any other new event... It should go into
regular Events Table, and then, if it's ALSO a Special Event, it should also
go in the SpecialEvents Table. Then and only then woud it go into the
EventAssociations Table, which stores Relations between events.
The concept of GroupID, Name it something Else like "RelationID" i just a
way t oassociate all the events that are related t oone another, but allow
events to be related in overlapping groups...
Say you had the following
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Then Event 1 is related to Event 3, and Event 5 through one "relationship",
(Group 1)
and to Events 2 & 5 through another overlapping relationship...
If the relationships cannot overlap, then you would need a different
structure.
"C TO" wrote:
> Having said in #2c in my previous response, the fact I removed TypeCode fr
om
> the PK, I would have to create a different EventID for a related
> specialEvent, which is preferable and good. It is prefereable because that
is
> consistent with the convention. It is good because another TypeCode event
is
> another event, not the same event. Because of that, however, if I want to
be
> able to track the changes in the event, for example, today two tasks have
> been added for that event, and because they are for the same place, reason
> (TypeCode), by the same people, I do not want to add new event, then I nee
d
> to have task table?
> Create Table Task
> (EventID Integer Not Null,
> TaskID Integer Not Null,
> StartDate DateTime Null,
> EndDate DateTime Null
> Primary Key (EventID, TaskID)
>
> )
> Good thing about doing this also includes SpecialEventHistory table may be
> avoided. Bad thing, for example may be each time I need know the status of
> the event with a specific TypeCode, I will need to, again, join this table
.
> How would you approch this?
> Please note that I do not have specific requirements other than I mentione
d.
> What we design now should cover for most, if not almost all, possible
> scenarios.
> "CBretana" wrote:
>|||OK, let do a select illustration here:
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Select EventID from EventAssociation E
where Exists ( Select 1 from EventAssociation G
Where E.GroupID = G.GroupID and EventID = 2)
Returns:
EventID
--
1
2
5
7
To get the same result, without a groupID, I have to do the following:
EventID RelatedEventID
1 3
1 2
2 5
3 5
2 7
5 7
3 1 -- this insert may or may not happen
Select Case when EventID = 2 then RelatedEventID
when RelatedEventID = 2 then EventID
end as EventID
from EventAssociation G
Where 2 in (EventID, RelatedEventID)
What would be the punishment? Obviously, I must have not seen the benefits
of GroupID here.
"CBretana" wrote:
> When you "Add" A related event to a special Event, that insert should
> initially be handled exactly as any other new event... It should go into
> regular Events Table, and then, if it's ALSO a Special Event, it should al
so
> go in the SpecialEvents Table. Then and only then woud it go into the
> EventAssociations Table, which stores Relations between events.
> The concept of GroupID, Name it something Else like "RelationID" i just a
> way t oassociate all the events that are related t oone another, but allow
> events to be related in overlapping groups...
> Say you had the following
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Then Event 1 is related to Event 3, and Event 5 through one "relationship"
,
> (Group 1)
> and to Events 2 & 5 through another overlapping relationship...
> If the relationships cannot overlap, then you would need a different
> structure.
>
> "C TO" wrote:
>|||No, to get related Events with GroupID structure, you just
Select Distinct O.EventID
From EventAssociation E
Join EventAssociation O
On O.GroupID = E.GroupID
Where E.EventID = 2
This is "Cleaner" In that it is asking "Show me all the Events that are in
any of the the Groups Event 2 is in", and it doesn't imply any ordering or o
f
one event to the other... As your design structure does... (EventID,
RelatedEventID implies a directional, binary relationship, not a grouping o
f
2 or more events that are all equally related to one another .
So what I suggested more closely represents a Business model where multiple
events (2 or many) are related to one another with no distinction between
which one is "master" and which ones are "subordinate". Your structure woul
d
be better id business model for a "relationship" which IS directional (in
each pair there is a left/right, or master/subordinate, or etc.) and if the
relationships are all binary (pairs), and there is therefore a distinction
between a direct Relationship and an Indirect one...
EventID RelatedEventID
1 3
3 2
-- here Event 1 is Directly related to Event 3,
and Event 3 is directly related to Event 2,
but Event 1 is only indirectly related to Event 2
I don't know which business model more accurately represents your business,
and without that knowledge I can not tell yo which of these structure would
be more appropriate..
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Your structure would be appropriate for relationships among people in a
Table, where you want to model Teacher-Student Relationships. It is:
1. Binary,
2. Drectional, (Just because I am your teacher, doesn't mean you are my
teacher), If we were each other's Teacher, we would put two records in the
table, one for [me to you], and one for [you to me]...
3. And there are direct and indirect relationships... If Bob is Mary's
Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher to
Mary...
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Thank you. The relationship in this model is not binary, but it is true for
(2) and (3). I am learning toward using GroupID now but the only thing left
is that it does not provide indirect relationship...
Perhaps indirect relationship with GroupID can be achived this way:
From the prev example of EventID 2, to related any direct relationship with
EventID 2, we get 1,5,7. If we are looking for one level up, anything that
has a relationship to EventID 2's siblings (1,5,7), we would look through
Groups that EventID 2 are in, right?
"CBretana" wrote:
> Your structure would be appropriate for relationships among people in a
> Table, where you want to model Teacher-Student Relationships. It is:
> 1. Binary,
> 2. Drectional, (Just because I am your teacher, doesn't mean you are my
> teacher), If we were each other's Teacher, we would put two records in th
e
> table, one for [me to you], and one for [you to me]...
> 3. And there are direct and indirect relationships... If Bob is Mary's
> Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher
to
> Mary...
> "C TO" wrote:
>
Labels:
anyeventid,
correction,
database,
events,
group,
knowing,
managing,
microsoft,
minor,
modeling,
mysql,
oracle,
regu,
server,
special,
specialevent,
specialeventidhaving,
sql,
standardeventid,
typecode
Data Modeling: manage events by groups
Scenario: The system needs to manage events by groups. There is no
hierachical concept in the events. Special events have different attributes
that need to be treated differently so I create a separate entity. Note that
the DeviceID has a many-to-one relationship with a LocationID (not in the
model). Not seen in the model also include PersonRole (PersonID,
PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
will need to report all the related events for a special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what would
be the advantages/di
vantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, it is drafted as below.
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])You have already posted this exact same message. Someone has graciously
donated his/her own time to respond; show a little consideration and track
your own threads.
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> Scenario: The system needs to manage events by groups. There is no
> hierachical concept in the events. Special events have different
attributes
> that need to be treated differently so I create a separate entity. Note
that
> the DeviceID has a many-to-one relationship with a LocationID (not in the
> model). Not seen in the model also include PersonRole (PersonID,
> PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
> will need to report all the related events for a special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3)
Please
> comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what
would
> be the advantages/di
vantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, it is drafted as below.
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
>|||I apologize. I use Google Group to search with my Author name, obviously thi
s
thread did not return. I thought that this thread got lost somewhere.
"Scott Morris" wrote:
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> attributes
> that
> one
> Please
> would
>
>|||Hello Scott, How to you track your own threads? Can you share it with us?
I posted lots of messages to different newsgroup? But sometimes, I forget to
follow it up...
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
>|||Any moderately useful newsgroup reader will do this. I use the one that
comes with outlook.
"js" <js@.someone@.hotmail.com> wrote in message
news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hello Scott, How to you track your own threads? Can you share it with us?
>
> I posted lots of messages to different newsgroup? But sometimes, I forget
to
> follow it up...
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
track
>|||is it outlook express?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCMgvbYUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Any moderately useful newsgroup reader will do this. I use the one that
> comes with outlook.
> "js" <js@.someone@.hotmail.com> wrote in message
> news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> to
> track
>
hierachical concept in the events. Special events have different attributes
that need to be treated differently so I create a separate entity. Note that
the DeviceID has a many-to-one relationship with a LocationID (not in the
model). Not seen in the model also include PersonRole (PersonID,
PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
will need to report all the related events for a special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what would
be the advantages/di
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, it is drafted as below.
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])You have already posted this exact same message. Someone has graciously
donated his/her own time to respond; show a little consideration and track
your own threads.
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> Scenario: The system needs to manage events by groups. There is no
> hierachical concept in the events. Special events have different
attributes
> that need to be treated differently so I create a separate entity. Note
that
> the DeviceID has a many-to-one relationship with a LocationID (not in the
> model). Not seen in the model also include PersonRole (PersonID,
> PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
> will need to report all the related events for a special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3)
Please
> comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what
would
> be the advantages/di
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, it is drafted as below.
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
>|||I apologize. I use Google Group to search with my Author name, obviously thi
s
thread did not return. I thought that this thread got lost somewhere.
"Scott Morris" wrote:
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> attributes
> that
> one
> Please
> would
>
>|||Hello Scott, How to you track your own threads? Can you share it with us?
I posted lots of messages to different newsgroup? But sometimes, I forget to
follow it up...
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
>|||Any moderately useful newsgroup reader will do this. I use the one that
comes with outlook.
"js" <js@.someone@.hotmail.com> wrote in message
news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hello Scott, How to you track your own threads? Can you share it with us?
>
> I posted lots of messages to different newsgroup? But sometimes, I forget
to
> follow it up...
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
track
>|||is it outlook express?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCMgvbYUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Any moderately useful newsgroup reader will do this. I use the one that
> comes with outlook.
> "js" <js@.someone@.hotmail.com> wrote in message
> news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> to
> track
>
data modeling training
I thought i would post this here.
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanksOne that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanksOne that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
Data Modeling Training
Does anyone have any good reccommendations for training in data modeling?The first thing I would say is that there is no training course that could
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/ref=sr_aps_books_1_1/026-9850805-9517237
Tinyurl:
http://tinyurl.com/23q5y
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/ref=sr_aps_books_1_1/026-9850805-9517237
Tinyurl:
http://tinyurl.com/23q5y
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
data modeling training
I thought i would post this here.
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanks
One that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanks
One that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
Data Modeling Training
Does anyone have any good reccommendations for training in data modeling?
The first thing I would say is that there is no training course that could
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos/...850805-9517237
Tinyurl:
http://tinyurl.com/23q5y
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
The first thing I would say is that there is no training course that could
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos/...850805-9517237
Tinyurl:
http://tinyurl.com/23q5y
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
data modeling training
I thought i would post this here.
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanksOne that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
not necessarily a SQL question. but kinda related.
Can someone recomend a good data modeling book?
Good for beginners.
thanksOne that I think is good for beginners yet pretty thorough,
easy to read is Designing Relational Database Systems by
Rebecca Riordan. It's an MS Press book.
-Sue
On Thu, 14 Sep 2006 11:52:40 -0500, "greg" <greg@.greg.com>
wrote:
>I thought i would post this here.
>not necessarily a SQL question. but kinda related.
>Can someone recomend a good data modeling book?
>Good for beginners.
>thanks
>
Data Modeling Training
Does anyone have any good reccommendations for training in data modeling?The first thing I would say is that there is no training course that could
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos... />
05-9517237
Tinyurl:
http://tinyurl.com/23q5y
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
possibly give you a proper foundation in database design. If you really
want to learn proper database design the place to start (and finish) is
Introduction to Database Systems by Chris Date. I will say that the book is
absolutely NOT "Database Design for Dummies" it is a very rigorous and
sometimes challenging treatment of the subject.
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Louis Davidson's book is good:
Professional SQL Server 2000 Database Design
Product Details:
a.. Paperback 600 pages (March 28, 2001)
a.. Publisher: Wrox Press Ltd
a.. ISBN: 1861004761
http://www.amazon.co.uk/exec/obidos... />
05-9517237
Tinyurl:
http://tinyurl.com/23q5y
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>|||Michelle,
Joe celko once made these suggestions..
Two good books for beginning and intermediate data modelers:
Database Design for Mere Mortals
Michael J. Hernandez
Addison Wesley, 1997
ISBN 0-201-69471-9
Data Modeling Essentials, 2nd Edition
Graeme C. Simsion
The Coriolis Group, 2001
ISBN 1-57610-872-4
This book (and others by the same authors) has whole schemas for common
business apllications.
The Data Model Resource Book
Len Siverston, W.H. Inmon, Kent Graziano
John Wiley & Sons, 1997
ISBN 0-471-15364-8
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"michelle" <michelle@.nospam.com> wrote in message
news:OZf7IwWLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Does anyone have any good reccommendations for training in data modeling?
>
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?
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
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?
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.
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.
Data Modeling Question - Medical Lab Test Results
I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>I believe your "vertical" approach sounds like a normalized design. You
would have a table of possible tests, another of patients and then an
associative object (junction table) that links a patient to a test. You'd
have to allow for multiples of the same test on the same patient, though
likely this would not occur on the same day. That table would likely look
like:
PatientTests
PatientID -> FK to Patients
TestID -> FK to Tests
TestDate
Remarks
Hopefully, the table of tests is straightforward, though it is possible that
certain tests would have attributes that would not apply to other tests. In
that case, you could have exclusive subtypes, with a parent test table -
containing the common attributes - and child tables with the specialized
stuff.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frankie" <A@.B.COM> wrote in message
news:%23P%23ICzxkGHA.2272@.TK2MSFTNGP05.phx.gbl...
I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>|||>> My question is this: What would you recommend for how we go about the
Do you know the benefits of having a normalized schema? Do you understand
the drawbacks of "denormalized" structures? You have already stated that
your preference is to identify the logical groupings of lab results and
place those in their own table. Therefore, the answer should be obvious.
Anith
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>I believe your "vertical" approach sounds like a normalized design. You
would have a table of possible tests, another of patients and then an
associative object (junction table) that links a patient to a test. You'd
have to allow for multiples of the same test on the same patient, though
likely this would not occur on the same day. That table would likely look
like:
PatientTests
PatientID -> FK to Patients
TestID -> FK to Tests
TestDate
Remarks
Hopefully, the table of tests is straightforward, though it is possible that
certain tests would have attributes that would not apply to other tests. In
that case, you could have exclusive subtypes, with a parent test table -
containing the common attributes - and child tables with the specialized
stuff.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frankie" <A@.B.COM> wrote in message
news:%23P%23ICzxkGHA.2272@.TK2MSFTNGP05.phx.gbl...
I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>|||>> My question is this: What would you recommend for how we go about the
Do you know the benefits of having a normalized schema? Do you understand
the drawbacks of "denormalized" structures? You have already stated that
your preference is to identify the logical groupings of lab results and
place those in their own table. Therefore, the answer should be obvious.
Anith
Data Modeling Question
I'm designing a database for a medical group that must keep track of various
"People"
Some are doctors and some are patients.
The client currently categorizes patients according to the type of
procedure(s) they have been seen for (e..g, "Jane is a Botox patient because
she had Botox injections" while "Ralph is a hair transplant patient because
he's had hair transplants." And on and on it goes). These procedures are
obviously not mutually exclusive given that any given patient can have more
than one type of procedure.
As I see the situation we have [Patients] and [Procedures]. We do NOT have
[patient types] even though that's how the client understands them. We just
have patients who have various procedures.
My whiz bang plan is to simply have a many-to-many relationship between
[Patients] and [Procedures].
This will work fine for identifying the so called "patient types"... just
SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
"the Botox patients".
Question 1: What would be a good way to classify a patient who has not yet
had any procedure? Say Bambi comes in and gets scheduled for Botox. The
doctors would want her to show up on reports as a "Botox patient" even
though she hasn't yet had the procedure.
Question 2: Given that [Doctors] and [Patients] are fundamentally different
"things" in this database, is it reasonable to have two tables - one for
Doctors and another for Patients... or is it recommended to have one table
("People") and then have some "PersonType" column that flags the person as a
doctor or a patient (and then have a bunch of NULLS for columns not relevant
to each row's designated "person type"). The one-table approach seems kind
of ugly. Just wanted some feedback on this before I go off and implement.
Thank you for your time and consideration.
-JThis has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have).
You may have an equivalent 'procedures' table. Procedures table would
likely have budget/costs associated with the procedure.
Another table would have the procedure history for a person. A person
could have multiple records in that table, each would have a key for
the person, the procedure name, the procedure key (for joining to
'procedures' table). This table would have a startdate and enddate for
the procedure. A person who is scheduled, but has not yet had the
procedure merely has a futuredated record in this table (based on
startdate). When the procedure is done, you give the record an
enddate.
Doctors and patients all belong in the same table b/c a doctor could be
a patient and vice versa. Each person has their own unique id and also
a second field which is the id of that persons PCP. So if you have:
name, uniqueid, PCPid
dr smith, 1, 0
dr jones, 2, 0
sick guy,3,1
dr williams,4,2
This means that sick guy goes to dr. smith. Dr williams goes to Dr.
Jones.
You likely WILL need a flag field that lets you clearly determine who
is a doc ('flagdoc' that has y/n or 1/0 for everyone).
Based on similar relationships, this is how my company does it.
Theoretically, maybe you don't have to put the procedure name in the
procedure history, but it's nice having it there.
HTH,
wayne|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... or is it recommended to have one table ("People") <<
Doctors and patients are logically different, so I would scrape the
idea of a general "Peoiple" table. Where is the "Treatments" table
that would show the dates (scheduled, actual, etc.), location,
doctor(s), etc. for Bambi's Botox?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
I would suggest you document people throughout their lifecycle with you. So
when the patient comes in, planning to get Botox, a row is created in the
Patients and PatientProcedures table. Another table would be related to the
patientProcedures table that would document the status of the relationship.
Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and
so on (I will assume you are
with the jokes since you started it out
with "Bambi" "). Then you have the best of both scenarios.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
Tough call. I would would not suggest the one table approach, but a table
for generic "people" attributes, and another for patient attributes. I
wouldn't have a PersonType in this case because a person could be both (the
key of the two subordinate tables would be the same as for the Person table
so a person could only be mapped once.) The existance of a row in the
patient table would indicate that the person is a patient. (Will you have
nurses, sleep makers (can't spell anesthesiologist) and such. Particularly
for billing and/or scheduling I would imagine.)
Now you have everything you need (I think) you can tell the type of patient
immediately, including their status "Planned" "Botox", "Scheduled" "Hair
Transplant" and after > 1 procedures takes place: "Planned" "Repeat"
"Botox". Then they can get specific about the types of patient that they
are looking at.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jordan R." <A@.B.COM> wrote in message
news:ux0URV4LGHA.3100@.tk2msftngp13.phx.gbl...
> I'm designing a database for a medical group that must keep track of
> various "People"
> Some are doctors and some are patients.
> The client currently categorizes patients according to the type of
> procedure(s) they have been seen for (e..g, "Jane is a Botox patient
> because she had Botox injections" while "Ralph is a hair transplant
> patient because he's had hair transplants." And on and on it goes). These
> procedures are obviously not mutually exclusive given that any given
> patient can have more than one type of procedure.
> As I see the situation we have [Patients] and [Procedures]. We do NOT have
> [patient types] even though that's how the client understands them. We
> just have patients who have various procedures.
> My whiz bang plan is to simply have a many-to-many relationship between
> [Patients] and [Procedures].
> This will work fine for identifying the so called "patient types"... just
> SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
> "the Botox patients".
> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
> Thank you for your time and consideration.
> -J
>|||wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice'
The futuredated record mentioned above could be a bit dangerous b/c
people will definitely back out on things. Our place has a whole
module for 'applicants'. If they are hired, then they get records for
jobs, etc. The data model for people who say they 'want to do something
in the future' could be pretty complex...|||The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the
above layout for employees and their dependents (which is a nice,
static relationship).
It does sound like the patients at your place can have numerous doctors
work on them over the course of time. Your db revolves around the
procedure--which can have one patient, one or two docs. Splitting out
may well be the best way to go.|||>> wouldn't it be pretty common for a doctor to also be a patient of his/her
own group practice? <<
No, not in the US; insurnace companies would go nuts. Can you say
"FRAUD!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> Tough call. I would would not suggest the one table approach, but a table
> for generic "people" attributes, and another for patient attributes.
Which country? The generic "people" approach seems to be the one taken
by the UK's National Health Service (the world's largest?) In the
interest of standards, they have published their data dictionary:
http://www.nhsia.nhs.uk/datastandar...
.asp?shownav=1
Jamie.|||Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a
minimum First Name, Last Name, mailing address, etc, perhaps some form of Id
Number, perhaps. The basics.
Some of these things in their list would be very offensive to Americans.
For some reason we will give up our "tax" number (social security number,
which is becoming too much of a citizen id number)
Then the patient would have a file number, perhaps if all records are stored
in a paper format, medical information, etc. This table would be related to
the appointment calendar, billing.
Then doctors information, abilities, schedule, etc.
By no means is this a required way to do it. Having two tables with some
minor overlap of information is not horrible when the two concepts are going
to have little interaction (for example, if you had to balance a doctor's
appointment schedule as a patient AND a doctor, this would be essential.)
Frankly if the only overlapping information in a medical system is that a
doctor is entered as a patient of another doctor AND a doctor of patients,
the world would rejoice at not having to explain why they want to see the
doctor 10 times.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> Which country? The generic "people" approach seems to be the one taken
> by the UK's National Health Service (the world's largest?) In the
> interest of standards, they have published their data dictionary:
> http://www.nhsia.nhs.uk/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||Louis Davidson wrote:
> Some of these things in their list would be very offensive to Americans.
> For some reason we will give up our "tax" number (social security number,
> which is becoming too much of a citizen id number)
That's why I opened with, 'Which country?" :) If the OP (or other
interested reader) is in the UK then choosing to follow the NHS model
is one way of resolving the quandary.
FWIW here we seem to be moving in the opposite direction e.g. identity
cards for all :(
Jamie
"People"
Some are doctors and some are patients.
The client currently categorizes patients according to the type of
procedure(s) they have been seen for (e..g, "Jane is a Botox patient because
she had Botox injections" while "Ralph is a hair transplant patient because
he's had hair transplants." And on and on it goes). These procedures are
obviously not mutually exclusive given that any given patient can have more
than one type of procedure.
As I see the situation we have [Patients] and [Procedures]. We do NOT have
[patient types] even though that's how the client understands them. We just
have patients who have various procedures.
My whiz bang plan is to simply have a many-to-many relationship between
[Patients] and [Procedures].
This will work fine for identifying the so called "patient types"... just
SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
"the Botox patients".
Question 1: What would be a good way to classify a patient who has not yet
had any procedure? Say Bambi comes in and gets scheduled for Botox. The
doctors would want her to show up on reports as a "Botox patient" even
though she hasn't yet had the procedure.
Question 2: Given that [Doctors] and [Patients] are fundamentally different
"things" in this database, is it reasonable to have two tables - one for
Doctors and another for Patients... or is it recommended to have one table
("People") and then have some "PersonType" column that flags the person as a
doctor or a patient (and then have a bunch of NULLS for columns not relevant
to each row's designated "person type"). The one-table approach seems kind
of ugly. Just wanted some feedback on this before I go off and implement.
Thank you for your time and consideration.
-JThis has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have).
You may have an equivalent 'procedures' table. Procedures table would
likely have budget/costs associated with the procedure.
Another table would have the procedure history for a person. A person
could have multiple records in that table, each would have a key for
the person, the procedure name, the procedure key (for joining to
'procedures' table). This table would have a startdate and enddate for
the procedure. A person who is scheduled, but has not yet had the
procedure merely has a futuredated record in this table (based on
startdate). When the procedure is done, you give the record an
enddate.
Doctors and patients all belong in the same table b/c a doctor could be
a patient and vice versa. Each person has their own unique id and also
a second field which is the id of that persons PCP. So if you have:
name, uniqueid, PCPid
dr smith, 1, 0
dr jones, 2, 0
sick guy,3,1
dr williams,4,2
This means that sick guy goes to dr. smith. Dr williams goes to Dr.
Jones.
You likely WILL need a flag field that lets you clearly determine who
is a doc ('flagdoc' that has y/n or 1/0 for everyone).
Based on similar relationships, this is how my company does it.
Theoretically, maybe you don't have to put the procedure name in the
procedure history, but it's nice having it there.
HTH,
wayne|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... or is it recommended to have one table ("People") <<
Doctors and patients are logically different, so I would scrape the
idea of a general "Peoiple" table. Where is the "Treatments" table
that would show the dates (scheduled, actual, etc.), location,
doctor(s), etc. for Bambi's Botox?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
I would suggest you document people throughout their lifecycle with you. So
when the patient comes in, planning to get Botox, a row is created in the
Patients and PatientProcedures table. Another table would be related to the
patientProcedures table that would document the status of the relationship.
Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and
so on (I will assume you are
with "Bambi" "). Then you have the best of both scenarios.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
Tough call. I would would not suggest the one table approach, but a table
for generic "people" attributes, and another for patient attributes. I
wouldn't have a PersonType in this case because a person could be both (the
key of the two subordinate tables would be the same as for the Person table
so a person could only be mapped once.) The existance of a row in the
patient table would indicate that the person is a patient. (Will you have
nurses, sleep makers (can't spell anesthesiologist) and such. Particularly
for billing and/or scheduling I would imagine.)
Now you have everything you need (I think) you can tell the type of patient
immediately, including their status "Planned" "Botox", "Scheduled" "Hair
Transplant" and after > 1 procedures takes place: "Planned" "Repeat"
"Botox". Then they can get specific about the types of patient that they
are looking at.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jordan R." <A@.B.COM> wrote in message
news:ux0URV4LGHA.3100@.tk2msftngp13.phx.gbl...
> I'm designing a database for a medical group that must keep track of
> various "People"
> Some are doctors and some are patients.
> The client currently categorizes patients according to the type of
> procedure(s) they have been seen for (e..g, "Jane is a Botox patient
> because she had Botox injections" while "Ralph is a hair transplant
> patient because he's had hair transplants." And on and on it goes). These
> procedures are obviously not mutually exclusive given that any given
> patient can have more than one type of procedure.
> As I see the situation we have [Patients] and [Procedures]. We do NOT have
> [patient types] even though that's how the client understands them. We
> just have patients who have various procedures.
> My whiz bang plan is to simply have a many-to-many relationship between
> [Patients] and [Procedures].
> This will work fine for identifying the so called "patient types"... just
> SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
> "the Botox patients".
> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
> Thank you for your time and consideration.
> -J
>|||wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice'
The futuredated record mentioned above could be a bit dangerous b/c
people will definitely back out on things. Our place has a whole
module for 'applicants'. If they are hired, then they get records for
jobs, etc. The data model for people who say they 'want to do something
in the future' could be pretty complex...|||The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the
above layout for employees and their dependents (which is a nice,
static relationship).
It does sound like the patients at your place can have numerous doctors
work on them over the course of time. Your db revolves around the
procedure--which can have one patient, one or two docs. Splitting out
may well be the best way to go.|||>> wouldn't it be pretty common for a doctor to also be a patient of his/her
own group practice? <<
No, not in the US; insurnace companies would go nuts. Can you say
"FRAUD!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> Tough call. I would would not suggest the one table approach, but a table
> for generic "people" attributes, and another for patient attributes.
Which country? The generic "people" approach seems to be the one taken
by the UK's National Health Service (the world's largest?) In the
interest of standards, they have published their data dictionary:
http://www.nhsia.nhs.uk/datastandar...
.asp?shownav=1
Jamie.|||Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a
minimum First Name, Last Name, mailing address, etc, perhaps some form of Id
Number, perhaps. The basics.
Some of these things in their list would be very offensive to Americans.
For some reason we will give up our "tax" number (social security number,
which is becoming too much of a citizen id number)
Then the patient would have a file number, perhaps if all records are stored
in a paper format, medical information, etc. This table would be related to
the appointment calendar, billing.
Then doctors information, abilities, schedule, etc.
By no means is this a required way to do it. Having two tables with some
minor overlap of information is not horrible when the two concepts are going
to have little interaction (for example, if you had to balance a doctor's
appointment schedule as a patient AND a doctor, this would be essential.)
Frankly if the only overlapping information in a medical system is that a
doctor is entered as a patient of another doctor AND a doctor of patients,
the world would rejoice at not having to explain why they want to see the
doctor 10 times.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> Which country? The generic "people" approach seems to be the one taken
> by the UK's National Health Service (the world's largest?) In the
> interest of standards, they have published their data dictionary:
> http://www.nhsia.nhs.uk/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||Louis Davidson wrote:
> Some of these things in their list would be very offensive to Americans.
> For some reason we will give up our "tax" number (social security number,
> which is becoming too much of a citizen id number)
That's why I opened with, 'Which country?" :) If the OP (or other
interested reader) is in the UK then choosing to follow the NHS model
is one way of resolving the quandary.
FWIW here we seem to be moving in the opposite direction e.g. identity
cards for all :(
Jamie
Subscribe to:
Posts (Atom)