0

I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count total clicks by country onto the contestant. At the bottom should be able to list how many clicks for each contestant by country in that specific poll. For clear picture something like in this website but as vote between two contestants.

Poll ID 1
Contestant ID 1
--- country ID 1
--- country ID 2
--- country ID 3
Contestant ID 2
--- country ID 1
--- country ID 2

Poll ID 2
Contestant ID 1
--- country ID 1
--- country ID 2
Contestant ID 3
--- country ID 1

Im planning to make schema:

Poll Table
id
poll_name
contestant_1_id (fk contestant id)
contestant_2_id (fk contestant id)

Contestant Table
id
contestant_name

Country Table
id
country_name

Vote Table
id
event_id (fk contestant id)
contestant_1_id(fk contestant id)
contestant_2_id(fk contestant id)
country_id (fk country id)
total

My question are:

  1. In the vote table, the mapping of contestants id should be from contestant table or poll table of contestant_1_id & contestant_2_id? Since these foreign keys in poll table are not unique because there can be other polls having same contestant.. Should i use pivot table?

  2. If i do use pivot table with many to many relationship, how could i possible build the vote table mapping to each poll id and contestant id?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.