locked
Design tables for recurring tasks/calendar(like google calendar) in sql server RRS feed

  • Question

  • Hi

     'm going to design Sql tables for recurring tasks/calendar(like google calendar). I mean the schedule of the task when the task is recurring. For this reason I considered two tables as follow:

    Create Table FrequentType(
    Id      Int Identity Not Null Primary Key,
    Title   nVarchar(256))
    
    Create Table Schedule(
    Id                  Int Identity Not Null Primary Key,
    FrequentType_Id     Int,
    FrequentInterval    Int,
    StartTime           DateTime,
    EndTime             DateTime)

    As a matter of fact, FrequentType contains 'Daily','Weekly','Monthly','Yearly'. In google calendar when we choose 'Weekly', we have option to choose which days of week too. So how can i handle that? then if we choose 'Monthly' we have two option 'Day of the month' and 'day of the week'. should I consider another tables for these?

    and also the endtime has three option with different value which is not obvious for me how to handle it.

    So thanks in advance.


    Alimardani

    Thursday, May 22, 2014 4:01 AM

Answers

  • >'Weekly', we have option to choose which days of week too. So how can i handle that? then if we choose 'Monthly' we have two option 'Day of the month' and 'day of the week'.

    I suggest separate tables: MonthlyDetail, WeeklyDetail

    PRIMARY KEY:  same  as Schedule (ID), it is also a FOREIGN KEY to the Schedule table.

    CREATE TABLE MonthlyDetail(
    ScheduleID INT PRIMARY KEY REFERENCES Schedule(ID),
    DayOfWeekOrMonth bit)



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by tracycai Wednesday, May 28, 2014 10:04 AM
    Friday, May 23, 2014 12:05 AM

All replies

  • Shouldn't FrequentInterval    be in FrequentType table?



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, May 22, 2014 5:28 PM
  • Hi,

    In my opinion it should n't be in frequenttype, since when you choose monthly as frequentType, then you can indicate every X month you want to repeat the task which shown by frequentinterval. but the question is how I consider another tables on which the user choose Weekly and monthly also.for weekly then i should save which day of week and for monthly i have to save two mentioned option. ???

    thanks.


    Alimardani

    Thursday, May 22, 2014 7:25 PM
  • >'Weekly', we have option to choose which days of week too. So how can i handle that? then if we choose 'Monthly' we have two option 'Day of the month' and 'day of the week'.

    I suggest separate tables: MonthlyDetail, WeeklyDetail

    PRIMARY KEY:  same  as Schedule (ID), it is also a FOREIGN KEY to the Schedule table.

    CREATE TABLE MonthlyDetail(
    ScheduleID INT PRIMARY KEY REFERENCES Schedule(ID),
    DayOfWeekOrMonth bit)



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by tracycai Wednesday, May 28, 2014 10:04 AM
    Friday, May 23, 2014 12:05 AM