SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar table


Calendar table

Author
Message
tt-615680
tt-615680
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5721 Visits: 1365
I have been trying to find out the best way to design a Normalized table for Calendar e.g. if planning to have a Relationship with a Client table what would the best way to design it e.g. what Primary Key to have on the Calendar table etc please?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)SSC Guru (454K reputation)

Group: Administrators
Points: 454316 Visits: 20597
I don't know if there's a best way. Bob has one way here: http://www.sqlservercentral.com/articles/calendar/145206/

Some people create a dimension table, and use an int as a PK with values such as 20180130. They would still have the date listed, but in this way, the PK is also human readable.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
lokeshmallampati
lokeshmallampati
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
Below is my sample calendar table.

S.no CalenderDate Dayofweek EndofMonth Weekend Holiday
1 01/01/2018 Monday No No yes
2 01/02/2018 Tuesday No No No


Brian Kelley
Brian Kelley
Keeper of the Duck
Keeper of the Duck (76K reputation)

Group: Moderators
Points: 76129 Visits: 1926
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.

K. Brian Kelley
@kbriankelley
drew.allen
drew.allen
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49793 Visits: 14720
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AM
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.

I would just use the date itself. It's only a single field instead of being a composite key. It's more human readable. I could see having an index on year/day of the year, but I would not make it the PK.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Brian Kelley
Brian Kelley
Keeper of the Duck
Keeper of the Duck (76K reputation)

Group: Moderators
Points: 76129 Visits: 1926
drew.allen - Tuesday, January 30, 2018 11:37 AM
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AM
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.

I would just use the date itself. It's only a single field instead of being a composite key. It's more human readable. I could see having an index on year/day of the year, but I would not make it the PK.

Drew

PK doesn't have to be human readable. And there's value to determining what happened on the Nth of the year over multiple years. Think about how you'd have to deconstruct the key, for instance, if you wanted to compare temperature highs over the last 40 years for the same day. Yes, you could look at month and day columns, but it's easier if you have a single column, excepting leap years, of course. But that throws anything of this sort off. Therefore, it really depends on what you're trying to do here. :-)


K. Brian Kelley
@kbriankelley
drew.allen
drew.allen
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49793 Visits: 14720
K. Brian Kelley - Tuesday, January 30, 2018 11:40 AM
drew.allen - Tuesday, January 30, 2018 11:37 AM
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AM
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.

I would just use the date itself. It's only a single field instead of being a composite key. It's more human readable. I could see having an index on year/day of the year, but I would not make it the PK.

Drew

PK doesn't have to be human readable. And there's value to determining what happened on the Nth of the year over multiple years. Think about how you'd have to deconstruct the key, for instance, if you wanted to compare temperature highs over the last 40 years for the same day. Yes, you could look at month and day columns, but it's easier if you have a single column, excepting leap years, of course. But that throws anything of this sort off. Therefore, it really depends on what you're trying to do here. :-)

No, the PK doesn't have to be human readable, but it certainly doesn't hurt. If you don't care about human readability, use an INT identity instead. Again it's going to be smaller and only a single field.

But is there enough value in "determining what happened on the Nth of the year over multiple years" to justify having it be the PK instead of a regular index? I've been working with SQL server almost 20 years and I've never once written a single query where I needed to use the Nth day of the year. I've used the end of the month, the end of the year, the end of the fiscal year, the end of the pay period, relative date ranges, and absolute date ranges, but never have I needed to use the Nth day of the year. Perhaps this is simply a reflection of the industries I've worked in and it might be more common in, for example, a weather database, but I haven't seen it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)SSC Guru (686K reputation)

Group: General Forum Members
Points: 686939 Visits: 45605
drew.allen - Tuesday, January 30, 2018 11:37 AM
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AM
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.

I would just use the date itself. It's only a single field instead of being a composite key. It's more human readable. I could see having an index on year/day of the year, but I would not make it the PK.

Drew


+1000 to that.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Brian Kelley
Brian Kelley
Keeper of the Duck
Keeper of the Duck (76K reputation)

Group: Moderators
Points: 76129 Visits: 1926
For most situations I agree with you. I would do it via more normal means. However, the reason I mentioned the example I did was to basically ask the question as to what the data will be used for. I’ve seen a few cases where the PK situation I mentioned is optimal. Usually this is for comparisons for “What happened comparatively across the years on this date?” Like weather.

K. Brian Kelley
@kbriankelley
Joe Celko
Joe Celko
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4259 Visits: 2916
tt-615680 - Tuesday, January 30, 2018 8:40 AM
I have been trying to find out the best way to design a Normalized table for Calendar e.g. if planning to have a Relationship with a Client table what would the best way to design it e.g. what Primary Key to have on the Calendar table etc please?
First of all, I suggest you download a copy of "Temporal Queries in SQL" by Rick Snodgrass at the University of Arizona website. It's a free PDF file. Obviously, the natural key for such calendar table is the calendar date and we happen to have a DATE data type. Now get a copy or popularization of the ISO 8601 standards for dates. You want to avoid keeping a local dialect in your calendar table because that's a function of the presentation layer and not the database layer of your tiered architecture.

The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_nbr INTEGER NOT NULL,
...);

INSERT INTO Calendar
VALUES ('2007-04-05', 42);
('2007-04-06', 43); -- Good Friday
('2007-04-07', 43);
('2007-04-08', 43); -- Easter Sunday
('2007-04-09', 44);
('2007-04-10', 45); -- Tuesday, back to work

To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';

Please post DDL and follow ANSI/ISO standards when asking for help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search