Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Red face Concept too complicated. Can't figure it out

    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.
    Last edited by Campos10988; 01-05-12 at 16:03.

  2. #2
    Join Date
    Jan 2012
    Posts
    2

    Thumbs up I think I solved it

    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What if Paco is AKA as SlayerB???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •