0

I need a table to hold downtime, basically a downtime event contains the following info:
EVENT_ID, START_TIME, END_TIME, SERVICES, CAUSES

The main issue is that I don't know how to set this up because I don't want to end up with a mess like this:

ID  |  EVENT_ID  |  START_TIME  |  END_TIME  | SERVICES  |  CAUSES
01          455       12:00          12:30      FINANCE     NETWORK
02          455       12:00          12:30      ADVANCE     NETWORK
...
13          455       12:00          12:30      REFRESH     DATABASE

Basically...for a single outage, I would have many many entries in the table, since if there are multiple services/causes, the table would in effect hold all combinations.

Is there a more efficient way of organizing this?

antonpug
  • 13,724
  • 28
  • 88
  • 129

1 Answers1

3

yes - normalize a little:

EVENT
------
event_id
start_tm
end_tm
description

EVENT_SERVICE
-------------
event_id
service_id
employee_id 
start_tm
end_tm
(other info as needed)

SERVICE
---------
service_id
description

CAUSE
-------
cause_id
description

EVENT_CAUSE
-----------
event_id
cause_id

edited to reflect ypercubes comment with a separate SERVICE table

Randy
  • 16,480
  • 1
  • 37
  • 55
  • Isn't there a `SERVICE` table missing? – ypercubeᵀᴹ Mar 08 '12 at 21:35
  • Hmm, but how are those linked together? For example, how is the main EVENT table linked to the list of services and causes? Also EVENT_CAUSE table would be pretty repetitive because it would list several causes for a single event ID? – antonpug Mar 08 '12 at 21:35
  • @ypercube - yes IF the services were repeatable... then the pattern would be just the same (as you know) as the EVENT – Randy Mar 08 '12 at 21:37
  • @antonpug the main event gets a unique event_id as a Primary Key, then in the EVENT_SERVICE, you refer to that event_id as a Foreign Key - that allows many services to be performed on each event.Similar for the CAUSE(s). – Randy Mar 08 '12 at 21:39