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).
What I have so far:
I came up with the following relation that contains all attributes:
(poll_id, question, option, participant_id, stars)
...and the following functional dependencies:
- poll_id → question
- poll_id, option, participant_id → stars
After applying the algorithm I have the following relations (keys are bold):
- (poll_id, question)
- (poll_id, option, participant_id, stars)
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 (dependencies, normalization rules)?