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

问题描述

只是一个快速的架构问题，真正上存储的日历数据。

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

我的问题是，你将如何去在mysql中存储这些数据和检索它的前端？

可能拥有所有可用的日期和储存不可用的日期？当然，如果他们再次设置为可用，删除的记录该日期？

干杯，
RJ

解决方案

  

可能拥有所有可用的日期和储存不可用的日期？当然，如果他们再次设置为可用，删除的记录该日期？

是的，我会与去，除了租房的时候，我不会删除的记录失效 - 你会很容易知道租用过期，因为它是在过去，所以你会自动继续租用以及历史

毕竟，有可用日期 ¹ 的无限多的，所以你不得不人为地限制日期的支持范围，如果你去周围的其他方法（和存储免费日期）。

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

另外，我猜你需要的服务是租来的（如租用者的名称），将有无处存放，如果租用了再$ P $通过不存在的行psented的情况下，一些额外的信息！

---

由于租赁的粒度一整天，我认为你正在寻找一个类似的数据库结构：

请注意RENTING_DAY PK自然如何prevents重叠。

另外，你可能沟RENTING_DAY并有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屋！