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

问题描述

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

我的问题是如何你将把这些数据存储在mysql中并在前端检索？

可能有可用的日期和存储不可用的日期？然后，如果它们再次设置为可用，请删除该日期的记录？

Cheers，
RJ

解决方案

可能有可用的日期和存储不可用的日期？那么如果它们再次设置为可用，那么删除该日期的记录？

是的，我会去，除了我租赁期满后不会删除记录 - 您很容易就知道租借已过期，因此，您也可以自动保留租借的历史。

之后所有这些都是无限数量的可用日期 ¹ ，所以你必须人为地限制支持的日期范围，如果你以其他方式（和存储的免费日期）。

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

此外，我猜想你想要一些额外的信息，以防一个服务出租（例如租客的名称），没有地方可以存储，如果租用由不存在的行代表！

---

由于租赁的粒度是一整天，我认为您正在查看类似于这样的数据库结构：

$

b $ b

或者，您可能会倾斜RENTING_DAY，并在RENTING中直接START_DATE和END_DATE，但这需要明确的范围重叠检查，可能无法理想地扩展。

Just a quick architecture question really on storing calendar data.

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.

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?

Cheers, 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:

Note how RENTING_DAY PK naturally prevents overlaps.

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屋！