Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

holidy closure table - data model

Posted on 2007-11-14
8
Medium Priority
?
439 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 500 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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 500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 500 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 70

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 70

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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