model multiple relationships of an entity with multiple others in relational database

Question

I am attempting to create a relational database to model fight sports information--contests between two opponents, each with three judges and a referee. I have tables that store information on each entity: Participant, Judge, and Referee. I also have a table that models the Contest itself.

How do I manage the relationships between these tables where Participants may participate in multiple contests, judges may judge multiple contests and referees may referee multiple contests?

Here is a minimal example of what I am trying to do.

This is the dbml:

Table Contest{
    contest_id[pk, increment]
    participant_1_id int [ref: > Partcipant.participant_id ]
    participant_2_id int [ref: > Partcipant.participant_id ]
}

Table Participant{
    participant_id[pk, increment]
    participant_wins int
    participant_losses int
    participant_draws int
}

Here is the generated SQL for PostgreSQL:

CREATE TABLE "Contest" (
  "contest_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "participant_1_id" int,
  "participant_2_id" int
);

CREATE TABLE "Participant" (
  "participant_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "participant_wins" int,
  "participant_losses" int,
  "participant_draws" int
);

ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_1_id") REFERENCES "Participant" ("participant_id");

ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_2_id") REFERENCES "Participant" ("participant_id");

Here are the error messages I get:

CREATE TABLE "Contest" (
  "contest_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "participant_1_id" int,
  "participant_2_id" int
);
psql:database/db.sql:5: ERROR:  relation "Contest" already exists
CREATE TABLE "Participant" (
  "participant_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "participant_wins" int,
  "participant_losses" int,
  "participant_draws" int
);
psql:database/db.sql:12: ERROR:  relation "Participant" already exists
ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_1_id") REFERENCES "Participant" ("participant_id");
psql:database/db.sql:14: ERROR:  column "participant_1_id" referenced in foreign key constraint does not exist
ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_2_id") REFERENCES "Participant" ("participant_id");
psql:database/db.sql:16: ERROR:  column "participant_2_id" referenced in foreign key constraint does not exist

Your SQL code appears to be fine. However, when executing CREATE TABLE ... again (when a table already exists) we get the error message ( see example dbfiddle.uk/x3ato0zc ). Solution: write a DROP TABLE ... for each of the tables into your script before the CREATE TABLE statements. (CAUTION: avoid using drop table on a live database.) — stefan
– stefan, Commented Nov 22, 2024 at 8:24
Modelling: If a contest has a single judge and a single referee, you are dealing with one-to-many relationships (as each judge/referee can be associated with several contests). Due to the fact that a contest has 2 participants, we are looking at a many-to-many relationship (contest <-> participant) in this case. This seems odd, but your model will need a "bridging table" or "intersection" between contest and participant. Skeleton example see dbfiddle.uk/v7iLzL4R — stefan
– stefan, Commented Nov 22, 2024 at 8:24
Please either ask about bad code with the obligatory minimal reproducible example & why you think it should return something else at the 1st subexpression that it doesn't give what you expect justified by reference to authoritative documentation or ask about your overall goal giving working parts you can do & ideally a minimal reproducible example. But please ask about the former 1st because misconceptions will get in the way of understanding the latter. How to Ask Help center — philipxy
– philipxy, Commented Dec 5, 2024 at 1:28

stefan · Accepted Answer · 2024-12-03 07:22:25Z

1

Your SQL code appears to be fine. However, when executing CREATE TABLE ... again (when a table already exists) we get the error message ( see example dbfiddle.uk/x3ato0zc ). Solution: write a DROP TABLE ... for each of the tables into your script before the CREATE TABLE statements. (CAUTION: avoid using drop table on a live database.)

Modelling: If a contest has a single judge and a single referee, you are dealing with one-to-many relationships (as each judge/referee can be associated with several contests). Due to the fact that a contest has 2 participants, we are looking at a many-to-many relationship (contest <-> participant) in this case. This seems odd, but your model will need a "bridging table" (aka "intersection" or "junction") between contest and participant. Skeleton example see https://dbfiddle.uk/v7iLzL4R

answered Dec 3, 2024 at 7:22

stefan

2,2622 gold badges15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

model multiple relationships of an entity with multiple others in relational database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related