I'm attempting to expand my knowledge of how database designs and concepts work. I'm working on a hobby web/mobile app for practice that will challenge me. However, I am running into an issue.
What my novel concept is that there are two buttons 'yes' and 'no'.
The buttons can only be clicked once a day.
If 'yes' is clicked then a counter keeps tracks of how many clicks (days) it has been pushed. Example "You've tied your shoes 95 days in a row!"
If 'no' is clicked then the counter "resets" for example "It has been 0 days since you've tied your shoe."
I've done some reading but I haven't been able to find too many examples that are like this. I've read several posts here (a sample of them below) ( implementing dynamic query handler on historical data
Storing stop/start points in a database
In an online questionnaire, what is a best way to design a database for keeping track of users all attempts? )
but I'm still pretty confused on how to move forward. My initial idea was to have a
user table (id | user | password)
points table (id | point | date)
If a person clicks on yes then it adds an entry into the points table. If they don't click anything then no entry occurs. If they click no it's a -1.
However, I'm not certain this is a solid way to build out the database especially if I want to track it.
My questions are:
Given the few tasks above, how to setup the database?
How would I be able to display a counter that once someone hits no it 'resets'? For example the database tracks that the individual has tied their shoes for the last 9 days in a row but once they say no they didn't tie their shoes the counter resets to 0.
How to proceed with analytics side, to show how many times they've clicked no, yes, so on but that's down the road.