sql - model multiple relationships of an entity with multiple others in relational database - Stack Overflow

admin管理员组

文章数量:1432178

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

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

• sql
• database
• relational-database

Share Improve this question Follow edited Nov 20, 2024 at 0:42 lucasCage asked Nov 19, 2024 at 4:47 lucasCagelucasCage 1933 bronze badges 3

• 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 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 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 Commented Dec 5, 2024 at 1:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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

本文标签： sqlmodel multiple relationships of an entity with multiple others in relational databaseStack Overflow