Table with 2 foreign keys coming up from one primary

Question

I'm making this database with superheroes for one of my school projects and I have a superheroes table (obviously) and an enemies table. So the enemies table have two foreign keys:

bad_superhero_id

and

good_superhero_id

The purpose of this table is to link the good superheroes with the bad superheroes (their enemies) from the characters table (superheroes). The both foreign keys are taking values from the id of the superheroes table. The problem is that my teacher doesn't like this and I don't know why. I mean, I saw this example in a book called Beginning PHP5, Apache, and MySQL Web Development and I also asked my coworkers that have good experience in creating database structure. They said it's not a problem, but my teacher wanted me to give her example where this is used, because she doesn't think it's good relationship and wants me to create a stupid workaround that she thought of. I still think this is not a bad way to create this kind of relationship so I wanted to ask here to get third opinion on this problem. I will be grateful if you give your opinion so that I can understand is it bad, good or doesn't matter practice to use relationship like this.

EDIT:

CREATE TABLE superhero (
    id INT NOT NULL AUTO_INCREMENT,
    nick_name VARCHAR,
    align ENUM ('good', 'bad'),
    PRIMARY KEY(id)
)   ENGINE=INNODB;

CREATE TABLE enemies_link (
    id INT NOT NULL AUTO_INCREMENT,
    good_sh_id INT NOT NULL,
    bad_sh_id INT NOT NULL,
    PRIMARY KEY (id),

    FOREIGN KEY (good_sh_id, bad_sh_id)
      REFERENCES superheroes(id)
      ON UPDATE CASCADE ON DELETE RESTRICT
)   ENGINE=INNODB;

EDIT2: Yes, I forgot to add that I want that n to n connection. Let's say spider-man have venom and green goblin for his enemies and on the other hand venom has some other good superheroes as enemies and so on.

Answer 1

Your teacher may be right: you very likely should define both superhero and enemy ids as separate foreign keys:

FOREIGN KEY good_sh_id REFERENCES superheroes(id),
FOREIGN KEY bad_sh_id REFERENCES superheroes(id)

The syntax you specified, would instead specify superhero references as a composite foreign key. I have to admit I'm not sure what this even means. The only way composite foreign keys make sense to me is when you use them to reference a composite primary key.

Answer 2

I think you have the correct approach. I clearify and probably repeat what you have already designed with tis example.

table hero (in other words person)
person_id, name, good_bad

(by the way good_bad could vary, so consider if that is in the right place)

table  opponent
person_id, opponent_person_id, battlefield

So you can have different opponents on different battlefields The only problem you have is to assure double entries or a concept how to handle this: e.g.;

person_id = 7, opponent_person_id = 11, battlefield = Rome
person_id = 11, opponent_person_id =7, battlefield = Rome

in business this could be a realistic use:

table  employment
chief_person_id,employe_person_id, department
chief_person_id = 7, employe_person_id = 10, department= 1
chief_person_id = 7, employe_person_id = 11, department= 1
chief_person_id = 9, employe_person_id = 12, department= 2
chief_person_id = 9, employe_person_id = 15, department= 2
chief_person_id = 12, employe_person_id = 16, department= 2 (even  sub-hierarchies can be shown. see id=12)

Answer 3

Your design is not intrinsically a bad design but it needs work. You are using a cross/intersection table which defines an n-to-n relationship. These are used all the time in production databases, like the relationship between Student and Course where a student can be taking several courses and a course will have many students signed up. Yours is just referring both sides to the same table. That's fine too. A table of parts, for example, can contain both components and modules with a component used to make many modules and a module made up of many components.

In your particular instance, you have a flag which designates if the superhero is bad or good. That's good (although the concept of "bad hero" is somewhat jolting -- wouldn't "superbeing" be a better designation?), but the flag and the id must be defined together in a unique constraint/index. That may seem superfluous, since the id is a primary key and therefore unique all by itself. But a foreign key can only refer to a unique field or set of fields.

As for the cross table, you really don't need a separate id field. In fact, that opens up a possible chink in data integrity. When modeling, always try to make data integrity a prime factor. Make it as close to impossible as you can to get bogus data into the table. The table key will all be the foreign key fields as one big composite key. If a separate key is required by foolish design standards, then be sure to define the foreign key fields together in a unique index. Then you must enforce the values of the good/bad flags to insure the 'good' FK can only point to a 'good' superhero, and so forth.

CREATE TABLE superhero(
    id INT NOT NULL AUTO_INCREMENT,
    nick_name VARCHAR( 20 ),
    align ENUM( 'good', 'bad' ) not null default 'good',

    PRIMARY KEY( id ),
    constraint unique id_align_uq( id, align )
)   ENGINE=INNODB;

CREATE TABLE enemies_link(
  good_sh_id INT NOT NULL,
  good_align enum( 'good', 'bad' ) not null check( good_align = 'good' ),
  bad_sh_id INT NOT NULL,
  bad_align enum( 'good', 'bad' ) not null check( bad_align = 'bad' ),

  PRIMARY KEY( good_sh_id, good_align, bad_sh_id, bad_align ),

  FOREIGN KEY( good_sh_id, good_align )
    REFERENCES superhero( id, align )
    ON UPDATE CASCADE ON DELETE RESTRICT,

  FOREIGN KEY( bad_sh_id, bad_align )
    REFERENCES superhero( id, align )
    ON UPDATE CASCADE ON DELETE RESTRICT
)   ENGINE=INNODB;