assuming we have a scenario where a user can participate competitions by uploading picture(s) what would be a better way to create table relationships having the following tables USERS, COMPETITIONS, PARTICIPATED_COMPETITIONS and PICTURES with following relationships

USERS - PARTICIPATED_COMPETITIONS is 1toMany relation

PARTICIPATED_COMPETITIONS - COMPETITIONS 1to1 relation

having this information I would like to know if the options listed below have pros and cons over each other in terms of flexibility performance when creating a relationship with PICTURES table. Which would you consider using and why?

1- I could hold primary keys of user and competition tables as foreign keys in PICTURES table

2- I could just hold PARTICIPATED_COMPETITIONS primary key as a foreign key in PICTURES tables. PARTICIPATED_COMPETITIONS table already has relations to both USERS and COMPETITIONS tables.

Thank you

CodePudding user response：

Ok I think to understood the problem. In my opinion the two options have different advantages and disadvantages.

1. For the first option the only advantages is the possibility to create must simple query to retrieve data because you don't need to do many JOIN operation because the users primary keys is alredy in the picture but this solution have a disadvantages, the redundancy of data.
2. This solution has the coutrary, less redundancy of data but maybe query more complex.

In general I prefer the second solution but you should decide based to your needs, if you are a system with a lot of complex query and you need a very speed app maybe you should be choice the first option.

CodePudding user response：

Okay, so it depends on how Pictures are meant to be used beyond what you have mentioned.

• In your example to make Pictures have references to Users and Competitions, all Pictures must belong to a user and a competition. In that case, maybe they should be called Submissions. But this is a good model.

• In the other case, where we have a reference to this relation-table Participated_competitions, it seems like an unnecessary "middleman" for connecting the picture to one user's entry in a competition.

So, here's an alternative way you could model it, which would allow you to have Pictures outside of competitions. Perhaps profile pictures?

Here's the schemas:

Tables

Users(_id_, username)

Competitions(_id_, name)

Pictures(_id_, picture)

Relations

Participations(_user_, _competition_, picture)
user -> Users.id
competition -> Competitions.id
picture -> Pictures.id

(This is a composite key, both user and competition is the primary key.

This allows multiple users to participate in one competition, and one user can participate in different competitions. It won't allow one user to participate multiple times in the same competition, so they cannot submit multiple pictures to the same competition.

You could change it to:

Participations(_user_, _competition_, _picture_)
user -> Users.id
competition -> Competitions.id
picture -> Pictures.id

, if you want users to be able to submit multiple pictures for one competition.

Please note, that this will allow a user to reuse a picture for multiple competitions. If you want to limit this, add a UNIQUE(picture) constraint to Participations.

All in all, whether you create a table Pictures(user, competition, picture) like your point 1 or Participations(user, competition, picture) like in my example, the only real difference is what other uses you have for Pictures. Like I said, you could change the name to Submissions and it will be exactly what I've modeled here.

Hope this was helpful! :)

CodePudding user response：

1- I could hold primary keys of user and competition tables as foreign keys in PICTURES table

Like that you'd be able to store a picture, with a valid user and a valid competition though the user might never have competed in that competition. I other words that would allow inconsistent data and is therefore a bad option.

2- I could just hold PARTICIPATED_COMPETITIONS primary key as a foreign key in PICTURES tables. PARTICIPATED_COMPETITIONS table already has relations to both USERS and COMPETITIONS tables.

That, on the other hand, would enforce that a picture belongs to a participation of a user in a competition. So it's clearly the better option.

But, if a user can only take part via a picture in a competition, you could omit PARTICIPATED_COMPETITIONS completely and instead have the competition and the user referenced in picture. Then the pictures will implicitly show who took part in which competition.