Solved

holidy closure table - data model

Posted on 2007-11-14
8
431 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

14 Experts available now in Live!

Get 1:1 Help Now