如何优化事件日历的数据模型？

发布于 2024-10-10 22:59:33 字数 470 浏览 0 评论 0原文

我正在制定一个活动日历。基本功能是这样的：

每天有3种状态，“可用”、“不可用”和“需要确认”。每天都可以设置为一个状态（即一个事件）。每个事件都可以设置为每周或每月重复发生，或者根本不重复发生。

每个日历都特定于一个对象（每个对象都有自己的日历）。

日历没有“结束日期”：未来的任何给定日期都可能有事件。

我想象的数据模型是这样的：

Table: Calendar
id
user_id

Table: Status
id
label

Table: Event
id
calendar_id
start_date
status_id
recurring -- enum type: NULL, W, or M for weekly or monthly

这似乎是一种相当优雅的存储数据的方式，但我担心检索：获取给定日期的状态会相当复杂。

有没有更好或标准的方法来做到这一点？

原文

I'm building an event calendar. The basic functionnality is like this:

There are 3 states for each day, "available", "unavailable" and "confirmation needed". Each day can be set to a single state (i.e., an event). Each event can be set to be recurring either weekly or monthly, or not at all.

Each calendar is specific for an object (each object has its own calendar).

The calendars don't have an "end date": there can be an event at any given date in the future.

The data model I imagined was this:

Table: Calendar
id
user_id

Table: Status
id
label

Table: Event
id
calendar_id
start_date
status_id
recurring -- enum type: NULL, W, or M for weekly or monthly

This seems to be a fairly elegant way to store the data, but I'm worried about retrieval: it would be fairly complicated to get the status for a given day.

Is there a better or standard way to do this ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

太阳公公是暖光 2024-10-17 22:59:33

假设那天是 start_date （否则我误解了），格式对我来说似乎不错。稍后，您可能需要开始/结束日期，也许还需要开始/结束时间，在这种情况下，您将不得不输入时间戳。

为了检索数据，我创建了一个日期表，如下所示：

+------------+
| cday (PK)  |
+------------+
| ...        |
| 2011-01-01 |
| 2011-01-02 |
| 2011-01-03 |
| 2011-01-04 |
| 2011-01-05 |
| 2011-01-06 |
| ...        |
+------------+

如果您需要在给定时间段内获取状态为 A（可用）的约会，您可以执行类似

SELECT ev.*
FROM cdays AS cd
JOIN event AS ev ON (
  CHECK_RECCUR(cd.cday, ev.day, cd.recurring)
)
WHERE TRUE
  AND cd.cday BETWEEN "given_start" AND "given_end"
  AND ev.day < "given_end"
;

CHECK_RECCUR() 的操作，该函数将检查如果 cday 在重复范围内，就像这样：

CREATE FUNCTION CHECK_RECCUR(cday DATE, start_date DATE, recurring CHAR(1))
BEGIN
  IF cday < start_date
  THEN RETURN FALSE
  END IF;
  SET dformat = CASE recurring
    WHEN 'W' THEN '%W'
    WHEN 'M' THEN '%d'
    WHEN 'Y' THEN '%m-%d'
    ELSE ''
  END;
  RETURN (DATE_FORMAT(cday, dformat) == DATE_FORMAT(start_date, dformat));
END
;

未测试，但这就是我要做的

Assuming that day is a start_date (otherwise I misunderstood), the format seems not bad to me. Later on maybe you will need to have start/end dates and maybe start/end time, in that case you will have to put timestamps instead.

In order to retrieve the data, I'd have a dates table created like:

+------------+
| cday (PK)  |
+------------+
| ...        |
| 2011-01-01 |
| 2011-01-02 |
| 2011-01-03 |
| 2011-01-04 |
| 2011-01-05 |
| 2011-01-06 |
| ...        |
+------------+

If you need to get the appointments with the status A (available) for a given period, you can do something like

SELECT ev.*
FROM cdays AS cd
JOIN event AS ev ON (
  CHECK_RECCUR(cd.cday, ev.day, cd.recurring)
)
WHERE TRUE
  AND cd.cday BETWEEN "given_start" AND "given_end"
  AND ev.day < "given_end"
;

CHECK_RECCUR() would be a function that checks if cday is in the scope of reccurence, like that:

CREATE FUNCTION CHECK_RECCUR(cday DATE, start_date DATE, recurring CHAR(1))
BEGIN
  IF cday < start_date
  THEN RETURN FALSE
  END IF;
  SET dformat = CASE recurring
    WHEN 'W' THEN '%W'
    WHEN 'M' THEN '%d'
    WHEN 'Y' THEN '%m-%d'
    ELSE ''
  END;
  RETURN (DATE_FORMAT(cday, dformat) == DATE_FORMAT(start_date, dformat));
END
;

Not tested but this is what I'd do

回复收藏 0 原文