I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture.
A poll has a unique id (poll_id), a question (question) and multiple options (option) that can be voted on. A participant (participant_id) can vote for each option by giving it one to ten stars (stars).
I came up with the following relation that contains all attributes:
(poll_id, question, option, participant_id, stars)
...and the following functional dependencies:
After applying the algorithm I have the following relations (keys are bold):
At this point the schema should be in BCNF, but it's easy to see that I can't just create a poll with a question and some options, because with this design options can only exist if there are also participants who have voted. I should probably have another relation (question,option). Did I miss something important in the design?
My ideas so far: