如何构建一个表来检测mysql中过期的许可证？

I'm trying to design a table that will help me determine if a licence has expired or not. I'd like suggestions for better ways to structure the table.

我正在尝试设计一个表格,以帮助我确定许可证是否已过期。我想建议更好的方法来构建表格。

Specifically I want to do the below 3 things:

具体来说,我想做以下三件事:

• I want to know if a licence exists

我想知道是否存在许可证

• I want to know if the licence has expired

我想知道许可证是否已过期

• I want to extend the licence if it hasn't expired

如果许可证尚未过期,我想延长许可证

I came up with the below table. When a user's licence is extended the first entry for that user's licence is marked as expired. I thought this method was good because if there was some kind of problem I still have the history to go by.

我提出了下表。扩展用户许可证后,该用户许可证的第一个条目将标记为已过期。我认为这种方法很好,因为如果出现某种问题,我仍然有历史可循。

----------------------------------------------------------------------------
|   user_id   |   licence   |    start    |    stop          |   expired   |
----------------------------------------------------------------------------
|     22      |    other    |   03JUL2010 |    03JUL2012     |    true     | 
|     55      |  commercial |   03JUL2012 |    03JUL2014     |    true     | <= marked as expired because it was extended.
|     55      |  commercial |   04JUL2012 |    03JUL2016     |    false    | 
----------------------------------------------------------------------------

NOTE: 04JUL2012 shows the day the licence was extended.

注意:04JUL2012显示许可证延长的日期。

Does this seem like a good approach? Is there anything you would change?

这看起来像是一个好方法吗?你有什么改变吗?

2 个解决方案

#1

I would have three tables, because it seems that there is a N:M (many-to-many relationship) between users and licenses:

我会有三个表,因为似乎用户和许可证之间存在N:M(多对多关系):

users
------------
user_id [PK]
...


users_has_licenses
----------------------
user_id [PK] (references users.user_id)
license_id [PK] (references licenses.license_id)
issued [PK]
expire_date


licenses
-------------------
license_id [PK]
...

The tables users and licenses are fairly straightforward. They store information intrinsic to users and licenses as standalone entities.

表用户和许可证非常简单。它们将用户和许可证固有的信息存储为独立实体。

In the users_has_licenses cross-reference table, rows are unique across three fields: user_id, license_id, and issued. issued is a datetime field representing the issue date or start date for the license. Since a user can renew licenses more than once, and you require a history to be kept of the license renewals, we include this field as part of the composite primary key.

在users_has_licenses交叉引用表中,行在三个字段中是唯一的:user_id,license_id和颁发。颁发的是一个日期时间字段,表示许可证的发布日期或开始日期。由于用户可以多次续订许可证,并且您需要保留许可证续订的历史记录,因此我们将此字段作为复合主键的一部分包含在内。

You can do without an expired field because you can already tell whether or not it's expired from the data itself, and you won't have to do routine updates to keep the data up to date. For example, to find a user's expired licenses, you can just execute the following query:

您可以在没有过期字段的情况下进行操作,因为您已经可以从数据本身判断它是否已过期,并且您不必进行例行更新以使数据保持最新。例如,要查找用户的过期许可证,您只需执行以下查询:

SELECT
    a.license_id
FROM
    (
        SELECT user_id, license_id, MAX(issued) AS issued
        FROM users_has_licenses
        WHERE user_id = <user_id here>
        GROUP BY user_id, license_id
    ) a
INNER JOIN
    users_has_licenses b ON
        a.user_id = b.user_id AND
        a.license_id = b.license_id AND
        a.issued = b.issued AND
        b.expire_date < NOW()   

It's a bit more hectic than you might expect, but only because you are retaining the data for past license renewals. In that case, you must do a group-wise maximum to make sure you're getting the most recent license period. The first sub-select figures out the most recent of each of a particular user's licenses, and then joins on the condition that the expire_date has already passed.

它比您预期的更加忙碌,但仅仅是因为您保留了过去许可续订的数据。在这种情况下,您必须执行分组最大值以确保您获得最新的许可证期限。第一个子选择计算出每个特定用户许可证的最新版本,然后在expire_date已经通过的条件下加入。

If you want to get all of the user's licenses (most recent period) whether or not they're expired and still want to tell whether they are expired or not, just change the INNER JOIN to a LEFT JOIN, and all non-expired licenses will have NULL values for the joined table.

如果您想获得所有用户的许可证(最近一段时间),无论它们是否已过期并仍想知道它们是否已过期,只需将INNER JOIN更改为LEFT JOIN,以及所有未过期的许可证将为连接表提供NULL值。

To renew a user's license, you just need one INSERT rather than an INSERT and an UPDATE such as would be the case with your current design:

要续订用户的许可证,您只需要一个INSERT而不是INSERT和UPDATE,例如您当前设计的情况:

INSERT INTO users_has_licenses VALUES (<user_id here>, <license_id here>, NOW(), NOW() + INTERVAL 4 YEAR)

EDIT: Addressing the asker's comment made to this answer:

编辑:解答提问者对此答案的评论:

I have one more question in regards to renewing the licence. If the user renewed their licence 6months into their first licence, how would you include that extra time in the INSERT INTO statement you listed above? I think the last value would need to be now() +remaining time + expiry period (or something like that). Unless I've misunderstood something. I'm just not sure how to get the remaining time.

关于更新许可证,我还有一个问题。如果用户将许可证续签6个月到他们的第一个许可证中,您将如何在上面列出的INSERT INTO语句中包含该额外时间?我认为最后一个值需要是now()+剩余时间+到期时间(或类似的东西)。除非我误解了什么。我只是不确定如何获得剩余时间。

Assuming you have chosen not to retain the history of past license periods, you can just do an UPDATE to the current record:

假设您已选择不保留过去许可证期限的历史记录,则可以对当前记录执行更新:

UPDATE users_has_licenses a SET
    a.issued = NOW(),
    a.expire_date = (
        SELECT 
            NOW() 
            + INTERVAL CASE WHEN DATEDIFF(expire_date, NOW()) < 0 THEN 0 ELSE DATEDIFF(expire_date, NOW()) END DAY 
            + INTERVAL 4 YEAR 
        FROM 
            users_has_licenses 
        WHERE 
            user_id = a.user_id AND 
            license_id = a.license_id
    )
WHERE
    a.user_id = <user_id here> AND
    a.license_id = <license_id here>

That will update issued to the current date and the expire_date to the current date + the remaining days of the old period + the regular expiry period(whatever it may be). If the past license has already expired, then the remaining days will be negative, in which case we would just add the regular expiry period.

这将更新发布到当前日期和expire_date到当前日期+旧时期的剩余天数+常规到期时间(无论它可能是什么)。如果过去的许可证已经过期,则剩余的天数将为负数,在这种情况下,我们只会添加常规的到期期限。

As a sidenote in regards to the above schema I posted to the original question, you no longer need to include issued as part of the primary key.

作为关于上述模式的旁注,我发布了原始问题,您不再需要包括作为主键的一部分发布。

#2

you are storing the same record twice. Instead you can go for a better design

你存储两次相同的记录。相反,你可以寻求更好的设计

table1

user-id  |  license  |  start | stop | expired | extended

table2

prim_key | user_id | extended_date | extended_date_expiry

In table1 extended column is a boolean value true or false.

在table1中,扩展列是布尔值true或false。

If extended is true then you can search for the date the user has extended to in table2.

如果extended为true,则可以在table2中搜索用户已扩展到的日期。

In table2 you can store multiple extended dates for same user and get the history also. The highest date to which it is extended would be the date of validity of that user_id.

在表2中,您可以为同一用户存储多个扩展日期并获取历史记录。它的最大延伸日期是该user_id的有效日期。