CODEWITHSUNDEEP
sql
Dan
Dan

Reputation: 3

Multiple columns from the same table in another table as foreign keys

I'm trying to make a game called "Odds On". You may have played this before.

  1. Two people decide on a range of numbers and a challenge/dare
  2. They both say their chosen number within the range at the same time.
  3. If the choices are the same, then whoever is 'it' has to complete the challenge/dare

I'm trying to learn and create an Ubuntu server with WinForm clients to make this game online.

With my current plan, I've run into a problem. I would like the game table to hold two user.username(s) as foreign keys as well as two round.roundID(s).

I've tried making the database in access and access said it could not enforce referential integrity

Can this be implemented in mySQL?? Perhaps using linking tables?

UML of what I'm trying to create

Upvotes: 0

Views: 28

Answers (1)

The Impaler
The Impaler

Reputation: 48770

I would avoid MS-Access since it has several limitations that could hamper your learning of relational databases.

You can have many relationships between each pair of tables. Your example -- in PostgreSQL -- can look like:

create table users (
  username varchar(20) primary key not null,
  password varchar(20),
  currently_logged_in int
);

create table round (
  round_id int primary key not null,
  user1_result int,
  user2_result int
);

create table game (
  game_id int primary key not null,
  user1 varchar(20) references users (username),
  user2 varchar(20) references users (username),
  round1_id int references round (round_id),
  round2_id int references round (round_id),
  lower_bound int,
  upper_bound int
);

See this example at db<>fiddle.

Upvotes: 1

Related Questions