Tables design similar to google calendar scheduling

Question

Tables design similar to google calendar scheduling

vishnu.vardhan414

Grasshopper

Points: 12
More actions
January 12, 2015 at 8:11 pm

#302378

Hi All,
I have a problem in Designing tables for events which are scheduling daily,weekly, monthly and yearly.
My current design for Scheduling is done for only weekly. Now i want to enhance it to Monthly and Yearly.
----------------------------------------------------------------------------------------------------------------------------
Create Table Activities
(
Id Int Primary Key Identity(1,1),
Name nvarchar(200) Not Null,
ActivityDate Date Not Null,
FromTime Time(0) Not Null,
ToTime Time(0) Not Null,
)
CREATE TABLE [dbo].[ActivitySchedules](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityId] [int] NOT NULL,
[ScheduleDay] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON
---------------------------------------------------------------------------------------------------------------------------
Can anyone help me in this design please.
Thank you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004719 More actions · Answer 1

vishnu.vardhan414 (1/12/2015)
Hi All,
I have a problem in Designing tables for events which are scheduling daily,weekly, monthly and yearly.
My current design for Scheduling is done for only weekly. Now i want to enhance it to Monthly and Yearly.
----------------------------------------------------------------------------------------------------------------------------
Create Table Activities
(
Id Int Primary Key Identity(1,1),
Name nvarchar(200) Not Null,
ActivityDate Date Not Null,
FromTime Time(0) Not Null,
ToTime Time(0) Not Null,
)
CREATE TABLE [dbo].[ActivitySchedules](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityId] [int] NOT NULL,
[ScheduleDay] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON
---------------------------------------------------------------------------------------------------------------------------
Can anyone help me in this design please.
Thank you.

Your current design isn't for weekly. It's for daily. It shouldn't be for either. It should be for "activity", which can certainly span more than one day. If it only occurs once, you're golden. If it occurs more than once (for weekly, monthly, or yearly), then take a lesson from the job scheduler tables in MSDB. Each activity is marked for a recurring schedule type and you can do one of two things or both, depending on your need. Either create a row for the next occurrence of the activity once the current activity is complete or preschedule many rows based on the schedule type for the activity.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vishnu.vardhan414 Grasshopper Points: 12 More actions · Answer 2

Hi Jeff Moden,

Thank you for your response. I will take your points into consideration and examine those.

Thank you

Jeff Moden SSC Guru Points: 1004719 More actions · Answer 3

You bet, Vishnu. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)