Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

holidy closure table - data model

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
-Dman100-
Asked:
-Dman100-
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
digital_thoughtsCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
imitchieCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imitchieCommented:
I find that spending 2-5 minutes a year is acceptable??
0
 
dportasCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
dportasCommented:
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
 
Scott PletcherSenior DBACommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now