Best approach to list of favorites

[phppup]

I want to get the results after asking 100 people to select their 3 favorite flavors of ice cream.

As I conceptualize the methodology, I can see I'd want an array of flavors to generate a group of checkboxes or a drop-down to avoid the conflict of misspellings.

But from the database perspective, what would be the Best Practice to implement?

Do I want a column for every flavor that receives an X when that row is submitted? Or do I want to input a 1 and then somehow total all columns whereby a descending list would run from most favorite to least?

Would a single column for flavor_01, flavor_02, flavor_03 be advisable? Then assess the votes for each flavor on each column and combine VANILLA from 01 and 02 and 03 etc?

(This might offer an opportunity to see how many people choose fudge swirl as their SECOND FAVORITE selection separate from the mass of voting, but this is probably not a relevant indicator)

Guidance, please.

 

[kicken]

If all you want is a simple count of votes, then just make one table that lists your flavors one per row with a second column that is a counter for the number of votes.

If you want to record additional data about the vote (such as who voted to prevent duplicates, or time that they voted) then you need two tables, one for your list of flavors and one for the votes.

The votes table would have a single row per vote and that row would contain the flavor that was voted for and any additional data you want to save.

Then to get your results, query the votes table for the row count grouped by flavor.

 

[maxxd]

To make it even simpler (maybe), you could actually have a single column in your table - use an enum with the three flavors as the values. Each row represents a vote - then you can count and group by value.

Not sure it's worth doing this over kicken's suggestion, just another way to look at it.

[phppup]

@kicken Sounds reasonable.

I was planning to have a single submittal for an individual's top 3 flavors so that I can get a broader result of flavors that are most popular, so I guess I'll need 3 more columns that I'll need to merge.

Obviously I'll create a safeguard so that no flavor can be submitted more than once when the entire form is submitted. All flavor choices must be unique.

@maxxd That was kinda the reason for my post. Too determine if there's a "preferred" method or more advisable choice.

I guess it kinda comes down to personal preference.

Maybe I'll just see who else votes and use this thread as my template. LOL

Life imitating art.

And meanwhile, I'll just have a bowl of vanilla caramel swirl.

Edited August 19, 2022 by phppup
Typos

[requinix]

7 minutes ago, phppup said:

I guess it kinda comes down to personal preference.

As long as your preference is not to add columns for the different flavors.

[maxxd]

I think I misinterpreted the original post. I read it as 'favorite of three flavors', not 'favorite three flavors'. Given that, I wouldn't recommend using an enum datatype. Personally, I'd probably go with a table to keep the flavors and another table to keep the votes. Count and group on select and everything's good to go. It's honestly just an over-engineered version of kicken's suggestion, but I have a tendency to over-engineer at the outset...

[kicken]

1 hour ago, phppup said:

so I guess I'll need 3 more columns that I'll need to merge.

Not sure what you mean by that.  Just take the list of flavors the user voted for and add them to your votes table as three rows.  If you want to preserve the order of the selections to determine first/second/third choice then that'd be another column on the votes table that saves the rank (1/2/3).