Solved

holidy closure table - data model

Posted on 2007-11-14
8
435 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Scott Pletcher
Scott Pletcher 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 25

Expert Comment

by:imitchie
ID: 20284830
I find that spending 2-5 minutes a year is acceptable??
0
 
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:Scott Pletcher
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:Scott Pletcher
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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