Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Entities with relations to the same type of entities?

    This may be a stupid question. Say you have a "faction" entity, and you need each "faction" to have some kind of information describing the relation it has with another faction. (In this case, actually ALL others.) What is the proper way to do this? I'm thinking it would be to have a "relation" entity which refers to both "factions" and in addition has the data (attributes) pertaining to the relation. Is that the right way to do this? (Any simpler method?) How would it look on an ER diagram? Is this a many-to-many relationship?

    If that's not clear, another example would be if you have a list of people. Each person can have a relation with every other person, say it's something more specific like "opinion". (Every person has an opinion of the others...) Would "opinion" simply be a separate entity that refers back to both "people"? (I presume simply by containing the keys needed to indentify both people.)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it would look like two boxes with two one-to-many arrows between them

    yes, "opinion" would have a composite primary key of two ids, each of which is a foreign key back to the person table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    22
    Thanks. It looks weird in the software I'm using, but I think it works and it seems to make sense too... some testing will reveal for sure...

  4. #4
    Join Date
    Dec 2004
    Posts
    54

    Many to Many with self

    Hey Man,
    That's rather common. Many entities relate to themselfs in a many to many. You're basically saying a faction can be related to one many other factions, or any two factions can be realted.

    What kind of db are you designing? Are you doing government work? The only use for faction that I know is to track organizations with questionable motives for existence?

    See the pic on the many to many. This is most flexible design

    Vmusic
    Attached Thumbnails Attached Thumbnails SampleRel.gif  

  5. #5
    Join Date
    Dec 2004
    Posts
    22
    It's a database for a game server to interact with. I think it will work out like that, I'll be testing it soon!

  6. #6
    Join Date
    Dec 2004
    Posts
    22
    Ok, maybe I'm overlooking something stupid here. I'm going to change the concept to "bond" (in the mutual friendship bond sense) so it's more appropriate.

    If I have these tables:

    tCharacter
    INTEGER character_id
    VARCHAR(45) name
    (Primary key character_id)

    tBond (a mutual relation)
    INTEGER primary_character_id
    INTEGER secondary_character_id
    INTEGER bond
    (Primary key primary_character_id, secondary_character_id)

    This would seem to work, but there's one problem--there doesn't seem to be any easy way to make consistent queries from the relations table. Also, it's possible (although pointless) to have TWO different relations for each pair of characters. Ex.

    tCharacter
    character_id: 0, Name: "char1"
    character_id: 1, Name: "char2"

    tBond
    primary_character_id: 0, secondary_character_id: 1, bond: 80

    That's one relation, and that's all that's needed. But you can also have this:

    tBond
    primary_character_id: 0, secondary_character_id: 1, bond: 80
    primary_character_id: 1, secondary_character_id: 0, bond: 50

    Both rows have unique identifying keys, yet different values for data (bond). Let's assume I only want ONE bond value per pair.

    Also, it's tougher to query the database for this information, because I don't know whether the character's character_id will be in the primary or secondary slot for each relation. Ex.

    SELECT bond FROM tBond WHERE primary_character_id = 0

    I'll get the 80, unless the alternate row (with the 50) is there and the 80 row isn't there. (I only want one of those rows to exist per pair of characters.)

    In other words what if this is the row that exists, and the bond:80 row doesn't exist at all?

    primary_character_id: 1, secondary_character_id: 0, bond: 50

    I wouldn't get anything at all, because in this entry the "0" character_id is in the secondary slot of the row. After I get no response back, I'd have to do a second query using WHERE secondary_character_id = 0 instead.

    Hopefully that makes sense... I'm probably missing something I should be doing.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you do seem to be stumbling around in the dark

    the first thing to nail down is whether the relationship is truly reflexive or not

    if fred loves mary, does mary love fred? in other words, does the relationship have a direction? if it does, then each pair of characters can exist as 0, 1, or 2 rows

    if the relationship is truly reflexive (e.g. equality, such as if one character is 1+1 and the other character is 4/2), then you do only need 0 or 1 row in the bond table

    if there's to be only 1 row per pairt of characters, then when you insert the row, always put the smaller id in the first position and the larger id in the second, as it makes the search sql a bit simpler for a given pair
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2004
    Posts
    22
    That's a great idea!!!!! So simple! (DUH!)

    The reason I changed from "faction" to "bond" is exactly what you pointed out--one faction may like another but it doesn't mean the other likes the first just as much. On the other hand, "bond" I'd like to be more of a mutual thing. I may decide I'm better off using two values anyway (maybe), or I may decide on a different option entirely, but I thought it best to figure out if/how I could do this before I go any further. Thanks for the suggestion!

Posting Permissions

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