January 30, 2018 at 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?
January 30, 2018 at 8:56 am
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.
January 30, 2018 at 10:59 am
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
January 30, 2018 at 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.
K. Brian Kelley
@kbriankelley
January 30, 2018 at 11:37 am
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AMAnother 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
January 30, 2018 at 11:40 am
drew.allen - Tuesday, January 30, 2018 11:37 AMK. Brian Kelley - Tuesday, January 30, 2018 11:21 AMAnother 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
January 30, 2018 at 12:16 pm
K. Brian Kelley - Tuesday, January 30, 2018 11:40 AMdrew.allen - Tuesday, January 30, 2018 11:37 AMK. Brian Kelley - Tuesday, January 30, 2018 11:21 AMAnother 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
January 31, 2018 at 8:14 am
drew.allen - Tuesday, January 30, 2018 11:37 AMK. Brian Kelley - Tuesday, January 30, 2018 11:21 AMAnother 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
Change is inevitable... Change for the better is not.
January 31, 2018 at 9:08 am
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
January 31, 2018 at 9:29 am
tt-615680 - Tuesday, January 30, 2018 8:40 AMI 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.
January 31, 2018 at 9:30 am
tt-615680 - Tuesday, January 30, 2018 8:40 AMI 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.
January 31, 2018 at 1:27 pm
I was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/
This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.
But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
January 31, 2018 at 1:35 pm
Darko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2018 at 2:25 pm
Jeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. 🙂
January 31, 2018 at 4:11 pm
Darko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. 🙂
If you're talking about any comments in the code then correct. TLDR; You should point them out. 😉 If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply