0

New to relational DBMS here. I have created table Users and table Sports.

Users Table

CREATE TABLE IF NOT EXISTS schema1.users
(
    user_id serial NOT NULL,
    user_name text NOT NULL,
    user_location text NOT NULL,
    sport_id integer,
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
    CONSTRAINT sports_pkey FOREIGN KEY (sport_id)
        REFERENCES schema1.sports (sport_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

Sports Table

CREATE TABLE IF NOT EXISTS schema1.sports
(
    sport_id serial NOT NULL,
    sport_name text,
    sport_category text,
    CONSTRAINT sports_pkey PRIMARY KEY (sport_id)
);

A person may be interested in more than one sports. For example, user A may be interested in basketball and baseball.

How this should be handled in order to be able to relate one user to multiple ids from the second table?

EMichael
  • 95
  • 1
  • 9
  • 1
    You create a third table of links between users and sports. Read up on https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model – Bergi Jul 29 '22 at 19:05
  • 2
    Remove `sport_id` from `users`, and create a table called `user_sports` with FKs to `users` and `sports`. – Mike Organek Jul 29 '22 at 19:06
  • 2
    This is called a [many-to-many](https://sqlmodel.tiangolo.com/tutorial/many-to-many/) relationship. You need a 3rd "join table" to connect users with their sports. – Schwern Jul 29 '22 at 19:35

0 Answers0