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 periodusually 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 period
> 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 period
> 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 nothave 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:
>
No comments:
Post a Comment