在 MySQL 中存储日历数据 [英] Storing Calendar Data in MySQL

问题描述

只是一个关于存储日历数据的快速架构问题.

Just a quick architecture question really on storing calendar data.

基本上，我有一个租赁服务数据库.在前端，有一个日历显示未来每个日期的可用"或不可用".在后端，用户可以在 jQuery 日历上将任何日期/日期范围设置为可用或不可用(1 或 0).

Basically, I have a database of services for rental. On the front end, there is a calendar to show either "Available" or "Unavailable" for every future date. In the back-end the user can set any date/date range to available or unavailable (1 or 0) on a jQuery calendar.

我的问题是您将如何将这些数据存储在 mysql 中并在前端检索它?

The question I have is how would you go about storing this data in mysql and retrieving it on the front end?

可能有所有可用的日期并存储不可用的日期?然后，如果它们再次设置为可用，则删除该日期的记录?

Possible have all dates available and store the unavailable dates? Then if they are set to available again, remove the record for that date?

干杯，RJ

推荐答案

可能有所有可用的日期并存储不可用的日期?然后，如果它们再次设置为可用，则删除该日期的记录?

Possible have all dates available and store the unavailable dates? Then if they are set to available again, remove the record for that date?

是的，我会这样做，除非我不会在租用到期时删除记录 - 您很容易知道租用已过期，因为它是过去的，因此您也会自动保留租用历史记录.

Yes, I'd go with that, except I would not remove the record when renting expires - you'll easily know a renting expired because it's in the past, so you automatically keep the history of renting as well.

毕竟，可用日期的数量是无限的¹，因此如果您反过来(和存储的免费日期)，则必须人为地限制支持的日期范围.

After all, there is infinite number of available dates¹, so you'd have to artificially limit the supported range of dates if you went the other way around (and stored free dates).

¹ 未来.而且，在某种意义上，过去也是如此.

¹ In the future. And, in some sense, in the past as well.

另外，我猜您需要一些额外的信息，以防租用服务(例如租用者的姓名)，如果租用由不存在的行表示，则无处存储！

Also, I'm guessing you want some additional information in case a service is rented (e.g. name of the renter) and there would be nowhere to store that if renting were represented by a non-existent row!

由于租用的粒度是一整天，我觉得你看的数据库结构是这样的:

Since the granularity of renting is a whole day, I think you are looking at a database structure similar to this:

注意 RENTING_DAY PK 如何自然地防止重叠.

Note how RENTING_DAY PK naturally prevents overlaps.

或者，您可以放弃 RENTING_DAY 并直接在 RENTING 中设置 START_DATE 和 END_DATE，但这需要明确的范围重叠检查，可能无法理想地扩展.

Alternatively, you might ditch the RENTING_DAY and have START_DATE and END_DATE directly in RENTING, but this would require explicit range overlap checks, which may not scale ideally.

这篇关于在 MySQL 中存储日历数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！