#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    19
    Rep Power
    0

    What is the proper way of making this type of relationship?


    Hey all... I'm working on a database right now and I have a table named 'Events'. The individual event records need to be able to relate to one another, and the relationship could be a one-to-none, one-to-one or one-to-many relationship. To accomodate this, I created another table called 'EventJoins' when has two foreign key fields, each pointing to the eventFK of the 'Events' table. Now, I can store the two event IDs in either field and link the two events together. To query this, I figured I could say, "Select * From tblEventJoins Where eventFK_1 = @eventID Or eventFK_2 = @eventID" Then, I would get a list of all related events.

    I feel this method may be the wrong approach, especially since I could have duplicates set up, or based on which event is being associated to the other, could create two similar rows which are different:

    Code:
    EVENT_ID1         EVENT_ID2
    16                13
    13                16
    16                19
    In this case, there are three events that are related (13, 16 and 19), assuming A = B, B = C, C = A... but, even if I am wrong in that assumption, two records are showing the same relationship.

    So, is there a 'right' way of doing this? Should I create another table which just has a primary key and, using that, group a bunch of events under the same primary key as a relationship?

    What do you guys think is the best practice here? Thanks in advance for your advice.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    19
    Rep Power
    0
    Just a friendly bump - still looking for peoples opinions on this topic. Thanks in advance.

IMN logo majestic logo threadwatch logo seochat tools logo