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