Examples of designing a database that handles point tracking?

Question

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:

1. Given the few tasks above, how to setup the database?

2. 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.

3. How to proceed with analytics side, to show how many times they've clicked no, yes, so on but that's down the road.

Answer 1

Firstly, whenever a user is involved, there is at least one additional state involved: what if the user does not interact or use your app on any given day?

This is important, because it leads to a variety of cases, e.g.

• If a user clicks yes on a Saturday, logons on Sunday but doesn't click a yes or a no, what do you want to show to the user?
• If a user clicks yes on a Saturday, doesn't logon on Sunday, and then clicks a yes on Monday, what do you want to show to the user? Is that 3 days, 2 days or just 1 day in a row?
• If a user clicks no on Saturday, doesn't logon on Sunday, and then clicks a yes on Monday, is that 2 days or just 1 day in a row?

Given that answers to these are not known, there could be additional cases to consider. The DB model usuall depends on what the complete behavior is going to be (i.e. the requirements).

For most flexible design, I would create the points table with the following columns:

UserId: acts as a foreign key for the id field in the user table
DateOfLastEntry: The date on which the user last clicked anything (regardless of yes or no)
NumberOfConsecutiveYes: number

Then code the following cases:

1. The entry for the user is created on the day the user first clicks a yes or a no.
2. For every click, compare the DateOfLastEntry with today's date. If the date matches, ignore user's click.
3. If the DateOfLastEntry is yesterday: I) If the user clicked Yes, add 1 to NumberOfConsecutiveYes, update the DateOfLastEntry to today. II) If user clicked No, set NumberOfConsecutiveYes to 0, and update the DateOfLastEntry to today.
4. For the day before yesterday, either use case 3 or add logic for whatever case you want to add.

Answer 2

If I understand correctly, you have 3 distinct bits of information: user, date, state (yes/ no)

So create a table with those fields (plus a unique primary key).

You can then query this table to find how many yeses there have been in a row, whether to show the buttons for the current day, yeses per month etc