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.)
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:
(Primary key character_id)
tBond (a mutual relation)
(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.
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.
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
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!