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.)

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

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...

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

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!

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.

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

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!