Comment On Journey to the Center of the Database

when will people ever learn that if it requires inputing dates to account for all future possibilities, that it will end up on the dailywtf

How dare they build a data warehouse?!

How does the rest of the daily WTF posting go: "Donnie quit his job two weeks later" ?



And why wasn't reservations_tb.AmountDue abstracted out?  Good
thing dates are abstracted and currency is not because no one disagrees
on dates and everyone agrees on currency......



.....wait....

Anonymous:

How dare they build a data warehouse?!

A date-a warehouse?

mother of all that is good and holy

how do i stop my eyes from burning?

Jesus tap-dancing Christ.

Normalization is like the Holy Grail... you can never quite get it, but you can make your life really miserable trying.

i've seen relational database educational materials with the very same design. i have no clue what is the reasoning behind.

i used to say: it is probably brilliant, but i don't understand how.

Apparently nobody sees the real value of this solution.
Let's say you ask a customer when he will pay and his answer is "when hell freezes over" .
In a normal database, all you can do is entering "31.12.2199" or null or some other magical value.
This system allows to enter a row into dates_tb with DateDesc="when hell freezes over" and DateDate=null until you find out when hell will freeze over.

Anonymous:

mother of all that is good and holy

how do i stop my eyes from burning?

Good work, now all of you start thinking of the next tired, yet somehow still slightly amusing variation on this comment for tomorrow's WTF.

Oops, was I supposed to phrase that in the form of "the real WTF is..."?

The Real WTF™ here is the shocking omission of normalizing the customers' FirstName & LastName fields. I mean, there are lots of people named "Tom" for example. We don't want to waste space with "Tom" repeated throughout that table, right?

For those that will complain that my posting has been done before (I'm not sure, it probably has), I reserve the right to re-run comments every so often. Just like the DailyWTF! :)

JBL:

Anonymous:

How dare they build a data warehouse?!

A date-a warehouse?

Date-a-whorehouse?

Anonymous:

mother of all that is good and holy

how do i stop my eyes from burning?

People usually try goggles round here, not that it ever seems to help.

As a punishment, Donnie should have entered the dates for the next 10 years in an arbitrary order, so that "order by DateId" gives a different result from "order by DateDate". When they find out, it's too late, and I think it's likely that many reports etc. will rather do "order by FooBarDateId" than "order by DateDate".

R.Flowers:

JBL:

Anonymous:

How dare they build a data warehouse?!

A date-a warehouse?

Date-a-whorehouse?

Date-a-Whore-House?

    I feel ashamed only to look at that!

Now, why is Donnie going and adding 10 years worth.  That's just asking for trouble.  Because he now has "ownership" of any date related problems in this application for the next 10 years.  (Come on, you know you're going to hear "Well Donnie inserted those dates, ask him why the curencry conversion calculation failed on xx/xx/xxxx date." ...or some such.)

The proper fix for this;

While max(dates_tb.DateDate) < date the primary support fukker returns.
    Add 1 date to table.

You now have a nice clean solution with proper hand off of responsibilities.

Oh yeah.  And don't tell the primary support fukker how many dates you added for your fix.  If he doesn't look for himself, it's his own fault that he gets called at 4:00 am.

Maybe I'm being unimaginative, but isn't putting in ten years of dates
still just perpetuating the wrongness? When the next support guy
catches this ten years on, when no one who remembers from this time
still works there, he'll have no one to get his back at all. Shouldn't
Donnie have set up a scheduled job to insert future dates (assuming all
the time that abstracting dates makes sense at all)? Even a daily job
to keep it one day ahead would be better than just sticking in more
dates by hand, woulnd't it?

Congratulations. This is the first WTF where I've physically let out a
huge sigh and shook my head instead of merely thinking it.

Simply put: Not surprised.

The naming conventions are beautiful. "DateDate" is personal favorite.

I wonder if they used a char(2) for the year, you know ... to save space.

ammoQ:

As a punishment, Donnie should have entered the dates for the next 10 years in an arbitrary order, so that "order by DateId" gives a different result from "order by DateDate". When they find out, it's too late, and I think it's likely that many reports etc. will rather do "order by FooBarDateId" than "order by DateDate".

Yes!!!!  I like it!  :)

don:

Maybe I'm being unimaginative,
but isn't putting in ten years of dates still just perpetuating the wrongness?
When the next support guy catches this ten years on, when no one who remembers
from this time still works there, he'll have no one to get his back at all.
Shouldn't Donnie have set up a scheduled job to insert future dates (assuming
all the time that abstracting dates makes sense at all)? Even a daily job to
keep it one day ahead would be better than just sticking in more dates by hand,
woulnd't it?

Wait, are you seriously proposing a well engineered solution to a fundamental
WTF? That's like saying: "Well sure his head was cut off, but instead of
mopping the blood of the floor, he should have tied a tourniquet around the stump,
and used sterile bandages on the wound..."



If it's broken beyond recognition, you select a quick and dirty solution and
get back to bed ASAP. Sheesh...

But you know, it's not like we couldn't join to a holiday table.

It's probably because someone couldn't figure out how to join on dates, or figured that the timestamps (or lack thereof) would get in the way...

I wonder if a datepart join (as in, join on day only, ignore time) breaks indexing?

At least Donnie knows to supply a sufficiently large band-aid (10 years instead of 1).

don:

isn't putting in ten years of dates
still just perpetuating the wrongness? When the next support guy
catches this ten years on, when no one who remembers from this time
still works there, he'll have no one to get his back at all.

I don´t know why... I don´t think this application will still be working 10 years from now....

But yeah, no soloution is good: inserting a few records is too short, inserting many is too much and irresponsible, programming a job to insertt 1, 10 , whichever amount of records is just as bad as ....

The only real thing to do is rebuild the freaking database, and comply with international standarization of where no table´s PK should be of a domain(platform) limited type..

The great stuff about MSSQL is it´s GUID'ed unique keys... Why would anyone use anything different.

And why wasn't reservations_tb.AmountDue abstracted out? Good thing dates are abstracted and currency is not because no one disagrees on dates and everyone agrees on currency......

In a time-saving measure, they abstract integers as they run across them while abstracting reals. So far they have '0' in the integer table, but they're getting ever closer to '1'.

Great!  I've been meaning to start a support firm specializing in Y2014 solutions.

But, where in the dates_tb table are the

isFileNotFound
isAlmostADate
isNotADate

fields???

Notice the IsHoliday column.  I'm guessing the table was meant to
enumerate holidays, and whoever made the other tables abused it or just
didn't understand its purpose.



Extra humor points go to whoever named a column "DateDate". 
Reminds me of someone who would frustrate maintenance programmers by
naming every local variable "temp";  if he needed a second local
variable, it was inevitably named "temptemp".

marvin_rabbit:

Now, why is Donnie going and adding 10 years worth.  That's just asking for trouble.  Because he now has "ownership" of any date related problems in this application for the next 10 years.  (Come on, you know you're going to hear "Well Donnie inserted those dates, ask him why the curencry conversion calculation failed on xx/xx/xxxx date." ...or some such.)

Perfect! That's why I hang out here! A Real programmer put in this response, not some wannabe. I wish I were one!

Date tables are fine.  Once you children get around the block a few times, you'll realize that date tables are a very good way to handle things like accounting dates.  (Hint: the problem is, accounting "dates" have little or nothing to do with calendar "dates"- they are simply strings with attributes.)

The WTF is a business process WTF- why is the IT department defining dates?  The accounting department should be defining dates, and the IT guys merely uploading them into a table. 

From the description, we can't tell whether this system is a data warehouse or an OLTP.  In either case it is still a WTF.  If it is an OLTP, dates should not be abstracted out into a separate dimension table.  If it is a data warehouse, I guess they never heard of an inferred member.

Larry

I have seen this so, so many times... it is the result of an automated database schema generation tool.

jvancil:

I have seen this so, so many times... it is the result of an automated database schema generation tool.

that tool would go great with my code geneartor that builds off the DB schema! i'll never have to write code again!

 

capcha > billgates

The obvious enterprise solution is to refactor the table into three tables - year, month and day.  Finally, an appropriate place for the 'IsWeekend' column.


---
The real wtf is that they are using robot berserk pup applesauce.

JBL:

Anonymous:

How dare they build a data warehouse?!

A date-a warehouse?

More like a date-unaware-house.

I disagree that all date tables are bad.  They are useful for data warehouse or financial apps not using OLAP if done correctly.  They allow for holidays as mentioned before, whether a date is a quarter end date or a month end date, calculating period start and end dates. Also solves a lot of fiscal quarter & year issues.  I wouldn't recommend storing the DateID, that is just annoying to work with. 

You know, it's time for everyone to adopt RFC 2550 dates.  I know—that was an April 1 RFC, but it makes sense!

This is a common enough pattern in data warehousing. Google on "dimensional data model" if you're still just making access databases of the nude scenes in your DVD collection. :-)

Anonymous:

mother of all that is good and holy

how do i stop my eyes from burning?

My eyes...the goggles do
nothing!

Just let a trigger add the dates as they are needed.  There's no reason that the dates in the table should be as dense as the developer.

Anonymous:

Just let a trigger add the dates as they are needed.  There's no reason that the dates in the table should be as dense as the developer.

could you imagine such an implementation?  *every* table that has one of these peusdo-date columns anywhere would have a trigger on it that would add "DateID's" to this table?  Yikes.

Anonymous:

I disagree that all date tables are bad.  They are useful for data warehouse or financial apps not using OLAP if done correctly.  They allow for holidays as mentioned before, whether a date is a quarter end date or a month end date, calculating period start and end dates. Also solves a lot of fiscal quarter & year issues.  I wouldn't recommend storing the DateID, that is just annoying to work with. 

I don't think too many people have stated that date or calendar tables are bad ... you hit the nail on the head about the "DateID" column, though -- that is the WTF .... in addition to the fact that this important table is apparently hidden from normal users and it is not clear when or how or where it should be maintained.  Sure, things were fixed by quickly adding 10 years of dates to the table to get things working again, but do you think that the "isHoliday" and other columns ended up being calculated correctly? 

One wonders why they haven't also normalised out all the Id columns - Just think how many times the same Id occurs across all the different tables! Scandalous - clearly this calls for a tblId containing id and idId columns. Then all other tables can link their id to tblId's idId.

don:

Maybe I'm being unimaginative, but isn't putting in ten years of dates
still just perpetuating the wrongness? When the next support guy
catches this ten years on, when no one who remembers from this time
still works there, he'll have no one to get his back at all. Shouldn't
Donnie have set up a scheduled job to insert future dates (assuming all
the time that abstracting dates makes sense at all)? Even a daily job
to keep it one day ahead would be better than just sticking in more
dates by hand, woulnd't it?

I am the one who had to deal with this.  The job originally did not require a great degree of computer knowledge, and they were lucky I was there.  (All new hires in this department now require programming knowledge).

This was an Access database--it was relatively easy to create the necessary data in Excel, and import it.

The date table was even more awful than the anonymized version.  There were fields such as DAY_NAME ("THU")  and  DAY_FULL_NAME ("THURSDAY"), not to mention julian_day and julian_week.  Naturally, the date, year, month, and day each got their separate field.

This database is now obsolete.

I know that in my company, we accept nothing less than 7th normal form databases.  For those of you ignorant in that level of normalization, we have an Integer table containing an entry for each integer value we use in the database, including an IsPrime field, an insertion date, as well as a key for looking up its string value in an internationalization table.

These kinds of software practices allow us to be an SEI level 8 shop with ISO9,000,000 certification.

Notice the IsHoliday column.  I'm guessing the table was meant to
enumerate holidays, and whoever made the other tables abused it or just
didn't understand its purpose.

Note this isHoliday column.  If the table was meant to store holidays, then this field would be redundant.

You guys are all retarded.  Since you guys are clearly ignorant on the topic of Data Warehouse design, I suggest you start by reading "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling".

The date dimension is important for storing fiscal, holiday, quarterly, etc... information along with the date.  Every business has different important fiscal days and holidays, so where else would you store that information besides a table?  Remember: THIS TABLE IS NOT DESIGNED FOR LIVE PURPOSES.  It is an OLAP design, only to be used for analysis.

For those who commented on the DateId: Kimball suggests that the DateId actually be stored in sequence with the dates, although this isn't necessarily a requirement.  Usually data warehouse information is aggregated along with a dimension's columns, for example "How much sales did we have in the last fiscal quarter?" which would necessitate a join on the Date table almost all the time.

Also, for the comment on the naming convention regarding DateDate: would you suggest it be called Date instead and possibly clash with an internal SQL function?  What else would you name it?

Looks like a wiring diagram for '65 VW Beetle !

Anonymous:

it is probably brilliant,

You're new here, aren't you?

(Link for the three of you who don't get the joke)

Where's the XML?