The need to sign in, how to do it well.

The mall receives a demand, has the following several requirements:

1. Added check-in function, 1th sign-in adds one point, 2nd sign-in increases 2 points, etc.

2. Consecutive check-in on the 6th day, 15th, 25th bonus points 50,70,100 points respectively.

3. It can be checked once a day.

4. Users can view history for several months of check-in records.

It's good to sign in and do it with Ajax.
But the problem is: according to the above requirements, how to store data better.

The first type:

Place the date of the check-in sign_record , separated by commas.

user_id	Date_month	Sign_record
1	2015-08	1,2,3,4,5,7,8,9,10 ...
2	2015-08	7,8,9,10 ...
3	2015-08	1,7,8,9,10 ...
...	...	...

The second type:

Put the date of the sign in sign_record ,

user_id	Date	Sign_record
1	2015-08	1
1	2015-08	2
1	2015-08	3
1	2015-08	4
2	2015-08	2
...	...	...

The third type:

I prefer the first of the above two, but may not conform to the three normal forms.
There is no other way to store it, please make a big weapon.

Reply content:

The mall receives a demand, has the following several requirements:

1. Added check-in function, 1th sign-in adds one point, 2nd sign-in increases 2 points, etc.

2. Consecutive check-in on the 6th day, 15th, 25th bonus points 50,70,100 points respectively.

3. It can be checked once a day.

4. Users can view history for several months of check-in records.

It's good to sign in and do it with Ajax.
But the problem is: according to the above requirements, how to store data better.

The first type:

Place the date of the check-in sign_record , separated by commas.

user_id	Date_month	Sign_record
1	2015-08	1,2,3,4,5,7,8,9,10 ...
2	2015-08	7,8,9,10 ...
3	2015-08	1,7,8,9,10 ...
...	...	...

The second type:

Put the date of the sign in sign_record ,

user_id	Date	Sign_record
1	2015-08	1
1	2015-08	2
1	2015-08	3
1	2015-08	4
2	2015-08	2
...	...	...

The third type:

I prefer the first of the above two, but may not conform to the three normal forms.
There is no other way to store it, please make a big weapon.

I've done a database design like this before, giving you a clue.
This obviously requires two tables for co-storage (to combine your first table with the second one)
Table A stores the
| UserID |last sign| ConSign Count |
| 1 | 2015-08-28 | 2 |
This consign Count stores the number of days that a user is continuously signed in
Table B is the same as the structure of your table two.
This is actually two needs, one needs to be called consecutive check-in to get points, another requirement is to view the attendance record
Table a you can understand as statistics
Each time a user signs in, the logic is

1. 1 user Check in 2 query form B to determine whether it is consecutive check-in (query yesterday's registration record)

     2.1 true 用户连续签到天数+1,并计算签到积分  2.2 false      2.2.1 判断是否补签（不太清楚你补签的需求是怎样，是前天签到的话就查询记录）         2.2.1.1 true 补签逻辑         2.2.1.2 false 将用户的连续签到天数重新置为1 3在表二中插入一条今天的签到的数据记录

Process End

sign(user_id,date_month,sign_records)

I also agree with the first type of landlord.
Because the condition of the query mainly falls on user_id and date_month on,
And then get the user's sign-in record sign_records ,
Then use explode to calculate the integral by separating the comma into the group.

It is recommended to date_month use timestamp storage:
strtotime('2015-08')Get 1438358400
The field type can select INT (10) Exactly, and the index is indexed to speed up the query.

Because the sign_records check-in record is an integer, you can intval() guarantee that the inserted value is an integer.
There is nothing wrong with using comma-delimited fields to save serialization or JSON encoding, and it is also convenient to use MySQL's FIND_IN_SET functions to sign_records query fields as conditions, which are the benefits of comma-delimited:
For example, check the user on August 1:

SELECT `user_id` FROM `sign` WHERE `date_month` = 1438358400 AND FIND_IN_SET('1', `sign_records`);

Personal insights are:
1. The Data table field "Datamonth" is changed to "Data Day" to record the check-in by days.
2. The specific score is based on the number of sign-in days:

a.比如：初始分值设为1，第二天签到的同时更新用户的签到分值，并且判断用户前一天是否签到。如果签到则第二天分值增加比前一天多1分。b.当增加的分值等于6时，表示用户连续签到了6天，则给当天的分值增加（1+49）[其中的"1"表示每天增加1，49是常数，为了凑要求的分值]分c.当增加的分值等于15时，表示用户连续签到15天，则给当天的分值增加（1+69）分。d.其他一次类推。

I'm not a big God, just talking about my opinion.

1. Using JSON storage
   MySQL is recommended in JSON, MySQL 5.7 native JSON format support.
   It is recommended to avoid the segmentation with some symbols, and the date is simpler. If complex data is prone to error. Can have a robust data structure on the data structure.

2. Using triggers
   You can use triggers to handle integrals or something.

The first, but add a field, is the number of consecutive check-in, the registration should be less than the behavior of the start

