Solved

holidy closure table - data model

Posted on 2007-11-14
8
430 Views
Last Modified: 2011-08-18
I am in the process of re-developing a page uisng asp.net that will pull in the content dynamically for a simple company holiday closure page:
http://support.compasslearning.com/Contact/closures.asp

My question is regarding handling the date fields in the db table creation.

Initially, I'm thinking the table structure should be as follows:

CREATE Table customer_service_closure_date
(
closure_Id int IDENTITY (1,1) PRIMARY KEY,
holiday varchar(50) NOT NULL
begin_date datetime NOT NULL
end_date datetime NOT NULL
)

I am going to create to an admin page so a service manager can update the closure dates as needed.  However, I started thinking there might be a better way to automate the closures dates within SQL Server using the date functions??  So, when the new year arrives, the closure dates are automatically calculated and populated?  The admin page could still have override capability if necessary.  I was just trying to think if there was a more efficient way to automate  how the closure dates are populated each year?

Maybe someone has done something similar and has some recommendations.

Any suggestions would be appreciated.
Thanks.
0
Comment
Question by:-Dman100-
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 125 total points
ID: 20284550
Another option would be to have FromMonth, FromDay, FromYear, ToMonth, ToDay and ToYear fields, along with an "EveryYear" bit field that would allow this. Its just more difficult to query against as you will have to format those columns into a date each time you wish to compare.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 20284754
In theory you could create a "holiday_definition" table (I prefer the term "reason" rather than "holiday", as it's a little more flexible: for example, if a big storm were to hit and you were to close because of that, it would seem odd to call it a "holiday").  You could even have codes that indicated how certain conditions were to be handled (for example, if Dec 25/Christmas falls on a Sunday, then we're closed Monday, but if it falls during the week, then we're closed that day and the second half of the prior day).

However, I wonder if it's really worth it.  Might be easier once a year just to have someone put in the days for the coming year.  Most companies only have 10-11 days max per year anyway.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 125 total points
ID: 20284803
you can either spend a lot of time finding a solution, or just use the table you have designed. if you know that a holiday (25/12) is the same date across all years, by all means insert it for the next 100 years. otherwise, for something like Good Friday, insert it for the next 3 years (confirmed dates), and keep updating it once a year. BTW, while there is a formula for Good Friday, the Catholic church has final say to confirm the date.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20284830
I find that spending 2-5 minutes a year is acceptable??
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 22

Assisted Solution

by:dportas
dportas earned 125 total points
ID: 20285094
Do you have a central source for this data in your organisation? It seems unlikely that the website is the only place where that information is needed. Don't duplicate data unless you have to.

Your schema looks far from ideal. What is closure_Id for? Do you really want to allow duplicate holidays with the same dates? I suspect not, so you could add a key constraint to prevent that. You see what I mean about finding a single, reliable source for the data...

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20289485
>> Do you really want to allow duplicate holidays with the same dates? <<

Definitely, if the table can be used for multiple countries.  Probably even within the U.S., since each state could in theory have a holiday (or two) different from other states.
0
 
LVL 22

Expert Comment

by:dportas
ID: 20289564
True, but they wouldn't be duplicates. They would either have different descriptions or would be identified by a state / country code. Then the key might be: (state_code, begin_date).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20289900
Ah, you're making the description part of the key?  I would tend to avoid that, although in the case of holidays it's probably not going to matter too often.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now