ERDs: How do you indicate mutually-exclusive encounters?

Question

If you have a character_instance and character entity in an ERD for a video game, and you want to indicate that something happens when a character entity encounters a character_instance entity, how do you do that? Here's the catch, though: a character_instance doesn't have the same relationship to its own character. Is there any special way to indicate this complicated relationship?

Answer 1

I don't know about ERD but, if I have understood correctly, this kind of business rule is very common when considering relationships between entities e.g. consider this example in SQL:

CREATE TABLE People 
(
 person_ID VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Marriages
(
 spouse_1 NOT NULL UNIQUE REFERENCES People (person_ID), 
 spouse_2 NOT NULL UNIQUE REFERENCES People (person_ID), 
 CHECK (spouse_1 < spouse_2)
);

In addition to enforcing the rule that a person cannot marry themselves, it also ensures that the same marriage is not entered a second time 'in the other direction' i.e. 'Albert is married to Victoria' is the same as 'Victoria is married to Albert' but only the row `('Albert', 'Victoria') may be entered into the database.

If this is too complex for ERD, consider using another more expressive model e.g. object role modelling.