DB Design - Linking to a parent without circular reference issues

Question

Hi all,

I'm having trouble coming up with a solution for the following issue.

Lets say i have a db that looks something like the following:

Issue Table

Id | Details | CreateDate | ClosedDate

Issue Notes Table

Id | ObjectId | Notes | NoteDate

Issue Assignment Table

Id | ObjectId | AssignedToId| AssignedDate

I'd like allow the linking of an issue to another issue. I thought about adding a column to the Issue table called ParentIssueId and that would allow me the ability to link issues, but i foresee circular references occurring within the issue table if i go through with this implementation. Is there a better way to go about doing this, and if so, how?

Thanks

Answer 1

You can add a join/link-table that would look something like this:

IssueLink

IssueId | LinkedIssueId

Where both columns are foreign keys to the Issue table.

This will allow you to link issues arbitrarily as well as allow a single issue to be linked to several others with the Parent-style relationship.

You'll want to put a unique index on the two columns so you don't end up with repeated data and also test to make sure that there is no condition such as this:

IssueId & LinkedIssueId = LinkedIssueId & IssueId

(which would result in a logical duplicate)

Look here: http://en.wikipedia.org/wiki/Junction_table The only difference is that the Junction table points to the same table to create the one-to-many relationship.

Answer 2

Create a table:

LinkedIssues
IssueIDa    pk composite primary key, fk to Issue table
IssueIDb    pk composite primary key, fk to Issue table

the PK will keep some duplicates away, but create a check constraint: IssueIDa<IssueIDb so you don't end up with a duplicate like:

row 1 IssueIDa=123
      IssueIDb=987

row 2 IssueIDa=987
      IssueIDb=123

however to prevent a circle like:

row 1 IssueIDa=123
      IssueIDb=987

row 2 IssueIDa=987
      IssueIDb=456

row 3 IssueIDa=456
      IssueIDb=123

you'll need a trigger that resolves the chain, and fails on a circle. Using a recursive CTE would be your best bet to detect this circle.

Answer 3

If an issue may be related to one other "parent" issue, then haveing a "ParentId" column in the table is fine (with NULL meaning no parent). If an issue may be related to more than one other issue, then a new "many-to-many" (or "link") table will be required.

In either case, checking for and preventing circular references (particularly when the "circle" encompasses three or more issues) will be tricky. Either you check for the circle during the UPDATE by basing the update on a CTE that checks for circular references and fails the update if any are found, or you could update in a stored procedure that first checks for circles, and only issues the update if the check passes. (Of course, you have to worry about concurrency issues--what if the data gets changed between your check and your update--which makes the CTE-based update preferrable, if more complex.)