0

I have a series of events (potentially hundreds) segregated into applications. Each one of these events has a dependency on others (except the first one, of course). I'm looking to map all of these dependencies in a table, but I'm not sure the best way to do so. One event can have multiple predecessors, or multiple successors, or both. There are also cases where A is a prereq for B, B is a prereq for C, but A is also a prereq for D, and C and D are both prereqs for E. (I can't assume that the dependency chain is linear.)

I already have a table with all of the events listed with some other details, and the event names are unique. My first thought was to have a "dependency" table that had an ID (PK) field, 'event' field containing the ID from the events table, and a "dependenton" field that also contained an ID from the events table. Obviously each event could have multiple entries if they are dependent on more than one other event. I believe this follows first normal form, but I'm trying to think if there is another design that would be more efficient or easier to work with.

Thank you.

Improve this question
  • You're describing a directed, cyclic graph. To what use will the dependencies be put? Are they just documentation, or will they be heavily read to determine, say, state changes? – Michael Green Apr 24 '17 at 12:31
0

Sounds like a "many:many" relationship.

See my tips on writing an optimal many:many table.

I assume you have a PRIMARY KEY for the Event table; perhaps the event name? It should be used in the many:many table.

Improve this answer
  • I have an auto-incrementing number for the primary key. Since the dependencies themselves are treated as entities in our environment (ie they can be moved, added, removed, etc.) I think not having a surrogate PK isn't a wise choice though. Other than that, it seems similar to what I already had in mind. – Tim S. Apr 21 '17 at 17:22
  • At least get rid of the id for the many:many table, itself. – Rick James Apr 21 '17 at 17:30
  • What's the benefit of getting rid of the ID field? It doesn't have to be used to uniquely identify a dependency, but it can be, and when serializing a request into JSON it makes it much easier. – Tim S. Apr 21 '17 at 17:54
  • (1) The pair of ids is UNIQUE, hence viable for being the PK. (2) The ID takes extra space. (3) Certain operations are slower because of going through ID unnecessarily. (4) The typical user fails to provide the optimal indexes. – Rick James Apr 22 '17 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.