MySQL架构：空列与联接

I have an application where I'll have repeating events. So an event can repeat by day, "every n days", by week, "every n weeks on Mon/Tue/Wed/etc", and by month, "every n months on the 1st,2nd,3rd,etc".

我有一个申请,我将有重复的事件。因此,事件可以按天,“每n天”,按周,“周一/周二/周三等每n周”,按月,“每1个月,第2个,第3个等等”重复。

What is the best way to handle this from a table design perspective? I can think of two ways but I'm not sure which one is better.

从表格设计的角度来看,处理这个问题的最佳方法是什么?我可以想到两种方式,但我不确定哪种方式更好。

1) 5 columns for the above, 1 for the day case and 2 each for week and month. Whichever ones are not being used would be null. In my application I could see the nulls and choose to ignore them.

1)上面的5列,白天的1列和周和月的2列。无论使用哪一个都是空的。在我的应用程序中,我可以看到空值并选择忽略它们。

2) Have a second table, say events_dateinfo or something, against which I'd JOIN for the query.

2)有一个第二个表,比如events_dateinfo或者什么,我将加入查询。

Seems like option 2 is probably more 'normalized' and what not, but does it strike you as overkill for such a simple thing? Also, if I were to go option 2, is there a way to translate rows into columns - that is, select the 2 week attributes for a specific event and have them treated as columns?

看起来像选项2可能更“标准化”而不是什么,但它是否会让你觉得这样一个简单的东西有点过分?另外,如果我要去选项2,是否有办法将行转换为列 - 也就是说,为特定事件选择2周属性并将它们视为列?

2 个解决方案

#1

If I understood right event can have more than 1 schedule (this is why you want " to translate rows into columns ").

如果我理解正确的事件可以有超过1个计划(这就是你想要“将行转换成列”的原因)。

You will need not 2 but 3 tables in this case; third one must be junction table. You can easily add new schedules if you need in the future with this scheme. So, something like this:

在这种情况下,您不需要2个而是3个表;第三个必须是联结表。如果将来使用此方案,您可以轻松添加新计划。所以,像这样:

table events (event_id, event_name, description)
table schedules (sch_id, schedule)
table event_schedule (event_id, sch_id)

There isn't PIVOT possibility in MySQL as I know, but you can use GROUP_CONCAT() function in SELECT; it'll be one row per event and all schedules for one event will be in one column.

据我所知,MySQL中没有PIVOT可能性,但您可以在SELECT中使用GROUP_CONCAT()函数;每个事件只有一行,一个事件的所有时间表都在一列中。

SELECT e.event_name AS Event, GROUP_CONCAT( s.schedule SEPARATOR ', ') AS Schedule
         FROM events e 
    (LEFT) JOIN event_schedule es
    ON e.event_id = es.event_id
    JOIN schedules s
    ON s.sch_id = es. sch_id
         GROUP BY e.event_name;

2 个解决方案

#1

更多相关文章

随机推荐