CODEWITHSUNDEEP
sqldatabase-design
Sebastian Zeki
Sebastian Zeki

Reputation: 6874

How to create join where a table is associated with more than one other table

I am trying to create an app where users can create and take part in challenges. The idea is that one user can create many challenges but each challenge can only be created by one user. Many users can enter one challenge and each entry can only be part of one challenge. Users can enter a challenge more than once

My confusion lies in trying to structure the table relationships. Issue 1) User:Challenge should be one to many but do I really need a separate Entry table (which would be 1:many for Challenge:Entry. If so, then do I need to associated Entry and User? Would I in fact be able to get away with a more simple many:many User:Challenge so that a user can create and be part of a challenge , and any particular challenge can have many users. If so then does this need a join table between User and Challenge?

Issue 2: If I do need a separate entry table as I suspect because a user can enter a challenge more than once, then can I keep this as a 1 challenge:many entries. In which case I will have

User Many:Many Challenge 1:Many Entry

but how do I then keep track of which entry belongs to which user?

Upvotes: 2

Views: 69

Answers (3)

Gabor
Gabor

Reputation: 1499

If I understood well your problem could be solved like that:

create table user (id int, name varchar(255))
create table challenge (id int, name varchar(255), created_by_user_id int)
create table entry (id int, name varchar(255), challenge_id int, user_id int)

So you could define a foreign ken on the challenge table to the user table. Same on the entry table to the challenge and user tables.

This setup would satisfy all your needs. Of course you will have to program a user interface :-)

Upvotes: 1

myroman
myroman

Reputation: 562

I agree to @Kritner answer and mine is an addition. To track which entry belongs to which user, you can write something like:

select u.userName, e.EntryId from T_Users u
inner join T_Entries e on e.userId = u.userId

Upvotes: 1

Kritner
Kritner

Reputation: 13765

I would see the schema looking something like this:

User
----
userId PK

Challenge
----
ChallengeId PK
creatingUserId FK to user.UserId

Entry
----
EntryId PK
ChallengeId FK to challenge.challengeId
UserId FK to user.UserId

this would allow only a single author of a challenge, but multiple participants (Entry) in a challenge. User would be related to both Challenge and Entry.

I think this is pretty close to (one of the options of) what you're describing.

Upvotes: 4

Related Questions