CODEWITHSUNDEEP
sqldatabasedatabase-design
0x1337
0x1337

Reputation: 1114

Attribute that implies different foreign keys

I've faced with a problem while studied databases. Suppose there are relations:

SomeEntity (
  id int PRIMARY KEY
);

CompletelyAnotherEntity (
  id int PRIMARY KEY
);

Competition (
  competitor1 FOREIGN KEY REFERENCES SomeEntity, 
  competitor2 FOREIGN KEY REFERENCES CompletelyAnotherEntity
  winner FOREIGN KEY REFERENCES ???
);

What type should has winner attribute in the Competition relation? What should it reference?

I came up with a solution of defining two attribute winner1 references SomeEntity and winner2 references CompletelyAnotherEntity. One of them can be null, but both simultaneously cannot be defined. I think it is not a correct solution, so help me resolve this issue properly.

Upvotes: 0

Views: 78

Answers (4)

Erwin Smout
Erwin Smout

Reputation: 18408

Why do you need to make winner reference anything ?

CHECK (winner in (competitor1, competitor2)) is what you really need. Winner as an FK to anything would still leave room for competitor3 to be the winner of (competitor1,competitor2). Does that seem reasonable to you ?

EDIT

I've deliberately ignored the issue of scheduled/ongoing competitions that do already have known competitors, but not yet a known winner.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57093

I'd use two relation variables for GladiatorWinsCompetition and AnimalWinsCompetition respectively (you could get away with having just one and inferring the other).

Cleaning up your schema a little (use more useful entity names, make attribute names consistent across relvars, always include data types):

Gladiator (
  GladiatorId int PRIMARY KEY
);

Animal (
  AnimalId int PRIMARY KEY
);

Competition (
  GladiatorId int FOREIGN KEY REFERENCES Gladiator, 
  AnimalId int FOREIGN KEY REFERENCES Animal,
  PRIMARY KEY ( GladiatorId, AnimalId )
);

GladiatorWinsCompetition (
  GladiatorId int, 
  AnimalId int,
  PRIMARY KEY ( GladiatorId, AnimalId ),
  FOREIGN KEY ( GladiatorId, AnimalId ) REFERENCES Competition
);

AnimalWinsCompetition (
  GladiatorId int, 
  AnimalId int,
  PRIMARY KEY ( GladiatorId, AnimalId ),
  FOREIGN KEY ( GladiatorId, AnimalId ) REFERENCES Competition
);

Upvotes: 1

philipxy
philipxy

Reputation: 15157

competitor(id)
gladiator(id) -- FK to competitor
animal(id) -- FK to competitor
competition(gid,aid,cid) -- FKs to gladiator, animal & competitor

If the DBMS cannot give you the column union type you need, have

gladiator(id,gtype)
animal(id,atype)

This also serves if the external language types of gladiators and/or animals are/is not a native DBMS where type, gtype or atype can be representations of them in some native DBMS type.

Observe how being in a given table is a way for a value to be typed independently of a DBMS or external type system.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I have a hard time thinking of a competition that has two different entities competing. I would expect something like this:

Competitors (
  CompetitorId int PRIMARY KEY
);

Competition (
  competitor1 FOREIGN KEY REFERENCES Competitors(CompetitorId), 
  competitor2 FOREIGN KEY REFERENCES Competitors(CompetitorId),
  winner FOREIGN KEY REFERENCES Competitors(CompetitorId)
);

Upvotes: 0

Related Questions