What things I should consider when having table relationships
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