I am have a C# application almost done which uses SQL Azure. It is a gang member tracking software. It has come to my attention that we might have to track accomplices according to reported incidents. So far this is my structure

GangMember
-------------
PK MemberID

Incident (Each member MUST have their own unique incident)
-------------
PK IncidentID
FK MemberID

Charges
-------------
PKChargeID

IncidentToCharge
-----------------
PKAutoID
FKIncidentID
FKChargeID

The problem arises with tracking associates. I do not want to track associates based off the GangMember table because we need to track associates that were brought in on the same day for the same incident (the reason we can't share incident is because 3 people might be brought in on the same incident but have different charges. Also for lookup and reporting services it is best each person have their own unique incident.

I thought about making a one to many table off incident

AssociationTable
----------------
PKAutoIDIndex
FKIncidentID
TiedIncidentID

this is fine I would have something like this in it

AssociationTable
----------------
PK1 FKIncidentID 1 TiedIncidentID 2 (IncidentID # 2 is tied to Incident #1)
PK2 FKIncidentID 1 TiedIncidentID 3 (IncidentID # 3 is tied to Incident #1)
PK3 FKIncidentID 1 TiedIncidentID 4 (IncidentID # 4 is tied to Incident #1)

(New Case)

PK4 FKIncidentID 8 TiedIncidentID 9
PK5 FKIncidentID 8 TiedIncidentID 10

Thats cool because I can just write a select query to get all associates

"SELECT TiedIncidentID FROM AssociationTable WHERE FKIncidentID = 1";

BUT if I load a record who's IncidentID is 2 then
"SELECT TiedIncidentID FROM AssociationTable WHERE FKIncidentID = 2";
will not work because 2 is not in that column. (Keep in mind this is a program I can not write these at will that's why this represents a problem");

So a way around this I see (complex and I really dont want to do this) is

"SELECT TiedIncidentID FROM AssociationTable WHERE FKIncidentID = 2";

if (Reader pulls NO results)
{
Then "SELECT FKIncidentID FROM AssociationTable WHERE TiedIncidentID = 2";

Here I would put the retrieved long value into a temp holder called "VALUE"

"SELECT TiedIncidentID FROM AssociationTable WHERE FKIncidentID = VALUE AND FKIncident != 2";

Then after this I can load all associated Incidents that are related to Incident 2.

}

I have thought ahead and realized What if the same guy (who has an incident already tied to 3 other incidents) comes in and gets a new incident, but he has 3 new associates?"

Then the above query pull gets even MORE complicated. And what if 2 of his associates are the same as last time but he has a new one. Then thats even MORE complicated. I am literally about to pull out my hair I can't see any further into this scenario or how to solve it simply. Please somebody help get this into perspective for me, but be gentle for I have been wrestling with this concept for a few days now. Suggestions have not been soaked in very easily.

What if I have a nullable "GroupID" with in each Incident record. If there are associates then they all get assigned the same group ID. That way when I am trying to pull associations all I have to do is
if (GroupID != Null)
SELECT FROM Incidents WHERE GroupID = x

So basically if an incident is logged in with an ID of 1 and 3 more incidents are tied to it then the number 1 will go into each incident record under groupID. This would never be duplicated because an incident number can never be duplicated and an incident can only have a relation once. Just putting my solution up in the event it might help someone out someday. Thanks anyways.

What if Paco is AKA as SlayerB???