I have a question about the proper design for the following project. I'm working on a website where different groups each have their own calendar where they can post events (and of course each group can only see their own events). Right now, recurring events are stored as lots of rows (like if an event recurs weekly for a year, that will add 52 rows). the structure is sort of like:
id - groupid - date - event - details - user
So the concern is that since recurring events create so many rows, eventually there will be lots of rows (especially as the number of groups increase) which will be clunky. Especially since the front page for each group lists upcoming events which means each visit will result in a query searching for "blah blah where groupid = $groupid" or something. I'm thinking of changing it so that recurring events can only take up one row, and then running a cron each week to create a new table that has only the events for the upcoming week (so that each recurring event then has it's own row). That way, when looking for upcoming events, it only has to look at the smaller table.
Most of what I've read so far has dealt with calendars for a single person or group, so the scaling factor is less of an issue than here (where there could be a lot of groups), and I haven't found anything effectively dealing with this issue.
If not obvious yet, I don't know what I'm talking about , so any advice would be much appreciated!