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/divantages?
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/divantages?
> 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
>