問題描述
只是一個關于存儲日歷數據的快速架構問題.
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.
畢竟，可用日期的數量是無限的1，因此如果您反過來(和存儲的免費日期)，則必須人為地限制支持的日期范圍.
After all, there is infinite number of available dates1, so you'd have to artificially limit the supported range of dates if you went the other way around (and stored free dates).
1 未來.而且，在某種意義上，過去也是如此.
1 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 中存儲日歷數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！