签到的需求,怎么做才好。
商城接到一个需求,有以下几个要求:
增加签到的功能,1号签到增加1个积分,2号签到增加2个积分,以此类推。
连续签到第6日,15日,25日分别奖励积分50,70,100个积分。
每天能补签一次。
用户能查看历史几个月的签到记录。
签到好做,用ajax提交就好。
可是问题是:根据以上需求,怎么存储数据较好。
第一种:
把签到的日期放在sign_record
中,以逗号隔开。
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... |
... | ... | ... |
第二种:
把签到的日期放在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 |
... | ... | ... |
第三种:
上面两种的话我偏向于第一种,但是可能不符合三范式。
有没有其他的方式来存储呢,请大大们支招。
回复内容:
商城接到一个需求,有以下几个要求:
增加签到的功能,1号签到增加1个积分,2号签到增加2个积分,以此类推。
连续签到第6日,15日,25日分别奖励积分50,70,100个积分。
每天能补签一次。
用户能查看历史几个月的签到记录。
签到好做,用ajax提交就好。
可是问题是:根据以上需求,怎么存储数据较好。
第一种:
把签到的日期放在sign_record
中,以逗号隔开。
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... |
... | ... | ... |
第二种:
把签到的日期放在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 |
... | ... | ... |
第三种:
上面两种的话我偏向于第一种,但是可能不符合三范式。
有没有其他的方式来存储呢,请大大们支招。
刚好我以前做过类似的一个数据库设计,给你一个思路
这个明显是需要两张表来进行共同存储(就是把你的第一张表跟第二张结合)
表A存储的为
| userid | lastsign| consigncount |
| 1 | 2015-08-28 | 2 |
这个consigncount存储的是用户连续签到的天数
表B跟你表二的结构一样
这其实是两个需求,一个需求叫连续签到获得积分,另一个需求叫查看签到记录
表A你可以理解为统计表
每次用户签到的时候判断逻辑为
-
1用户签到 2查询表B判断是否为连续签到(查询昨天的签到记录)
<code> 2.1 true 用户连续签到天数+1,并计算签到积分 2.2 false 2.2.1 判断是否补签(不太清楚你补签的需求是怎样,是前天签到的话就查询记录) 2.2.1.1 true 补签逻辑 2.2.1.2 false 将用户的连续签到天数重新置为1 3在表二中插入一条今天的签到的数据记录 </code>
Copy after login
流程结束
<code>sign(user_id,date_month,sign_records)</code>
我也认同楼主采用第一种.
因为查询的条件主要还是落在user_id
和date_month
上,
然后拿到用户的签到记录sign_records
,
再用explode以逗号分割转成数组进行积分计算.
建议date_month
使用时间戳存储:strtotime('2015-08')
得1438358400
字段类型刚好可以选择int(10),同时建立索引,加速查询.
因为签到记录sign_records
都是整数,可以用intval()
保证插入的值是整数.
以逗号分隔存入字段并无不妥,省去了序列化或者JSON的编解码操作,同时还方便使用MySQL的FIND_IN_SET
函数以sign_records
字段为条件进行查询,这都是逗号分隔的好处:
比如查询在8月1日签到的用户:
<code>SELECT `user_id` FROM `sign` WHERE `date_month` = 1438358400 AND FIND_IN_SET('1', `sign_records`); </code>
个人见解是:
1.数据表的字段“datamonth”改为“dataday”按天来记录签到。
2.具体的分值根据签到天数计算:
<code>a.比如:初始分值设为1,第二天签到的同时更新用户的签到分值,并且判断用户前一天是否签到。如果签到则第二天分值增加比前一天多1分。 b.当增加的分值等于6时,表示用户连续签到了6天,则给当天的分值增加(1+49)[其中的"1"表示每天增加1,49是常数,为了凑要求的分值]分 c.当增加的分值等于15时,表示用户连续签到15天,则给当天的分值增加(1+69)分。 d.其他一次类推。</code>
我不是大神,只是说说我的看法。
使用JSON存储
MySQL的话建议用JSON, MySQL 5.7原生JSON格式支持.
用某种符号进行分割建议尽量避免, 日期比较简单. 如果复杂的数据就很容易出错. 能有稳健的数据结构就用数据结构.使用触发器
可以使用触发器来处理积分什么的.
第一种,但是增加一个字段,就是连续签到次数,补签应该是出发比较少的行为吧

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics











JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,

A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total

Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.

What are the magic methods of PHP? PHP's magic methods include: 1.\_\_construct, used to initialize objects; 2.\_\_destruct, used to clean up resources; 3.\_\_call, handle non-existent method calls; 4.\_\_get, implement dynamic attribute access; 5.\_\_set, implement dynamic attribute settings. These methods are automatically called in certain situations, improving code flexibility and efficiency.

PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.

PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.

PHP is a scripting language widely used on the server side, especially suitable for web development. 1.PHP can embed HTML, process HTTP requests and responses, and supports a variety of databases. 2.PHP is used to generate dynamic web content, process form data, access databases, etc., with strong community support and open source resources. 3. PHP is an interpreted language, and the execution process includes lexical analysis, grammatical analysis, compilation and execution. 4.PHP can be combined with MySQL for advanced applications such as user registration systems. 5. When debugging PHP, you can use functions such as error_reporting() and var_dump(). 6. Optimize PHP code to use caching mechanisms, optimize database queries and use built-in functions. 7

PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.
