How many reminders can each user have? Thinking about myself, I would sometimes want reminders just before some event, and other times a day or two before the event (if I'm attending a meeting in this building, it is OK to be reminded about it one minute before the meeting starts, but I would have desired a somewhat longer notice before the Firebird conference in Bremen, Germany last week). So I'd say a datetime range could be appropriate even for reminders and that that value might not always be the same as the datetime of the event. However, if each user only has a handful of reminders, this will not make any difference speedwise.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Zd
Sent: 19. november 2010 13:43
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] DB design best practices - which solutionis faster?

Hello Peter!

When querying for events I query based on a date range.

But when querying for reminders, I query for UserID and IsReminder = 1 - I can't query for date range here.

If you take this into account, which is the best solution?

Thanks,
Zd

----- Original Message -----
From: Peter Faulks
To: firebird-support@yahoogroups.com
Sent: Friday, November 19, 2010 1:12 PM
Subject: Re: [firebird-support] DB design best practices - which solutionis faster?



What are you going to store in the calendar table?

Surely you would be querying based on a certain date range?

Index on a date column

SELECT userid, cal_date, whatever, whatever_else FROM calendar WHERE
cal_date BETWEEN <begin_date> AND <end_date> AND reminder = 1

On Fri, 2010-11-19 at 09:22 +0100, Zd wrote:
> Hello All!
>
> I'm looking for the fastest method to solve this issue.
>
> I'm writing a calendaring application. Different users' calendar entries are stored in a big Calendar table.
> Each calendar entry can have a reminder set - only one reminder/entry.
>
> Statistically, the Calendar table could grow to hundreds of thousands of records over time, while there are going to be much less reminders.
>
> I need to query the reminders on a constant basis.
>
> Which is the best option?
>
> A) Store the reminders' info in the Calendar table (in which case I'm going to query hundreds of thousands of records for IsReminder = 1)
> B) Create a separate Reminders table which contains only the ID of calendar entries which have reminders set, then query the two tables with a JOIN operation (or maybe create a view on them)
> C) I can store all information about reminders in the Reminders table, then query only this table. The downside is that some information needs to be duplicated in both tables, like in order to show the reminder, I'll need to know and store the event's starttime in the Reminders table - thus I'm maintaining two tables with the same values.
>
> What do you think?
>
> And one more question: The Calendar table will contain the calender of multiple users, separated only by a UserID field. Since there can be only 4-5 users, even if I put an index on this field, its selectivity is going to be very bad - which is not good for a table with hundreds of thousands of records. Is there a workaround here?
>
> Thanks!