- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
You know, these kinds of tables are heavily used in banking.
So let's say, in your infinite wisdom, that you used a Date column for the date for, oh, I don't know, the day a futures order is supposed to expire (can you guess what industry I worked in). Now, you need to generate reports for the traders stating what orders are expiring in the next 1, 2 and 3 days.
The beautiful part about working with financials people is that words like "tomorrow" and "in 2 days" don't mean "tomorrow", and they sure as hell don't mean "in 2 days". They mean "business day + 1" and "business day + 2" where the business day rolls at 4:00 PM for one trading floor, 5:00 PM for another trading floor, and 6:00 AM for the overnight market.
Now imagine that you have to support these rediculously obtuse rules across dozens of applications written in everything from Cobol to Java to C# to Lisp (I kid you not). It suddenly becomes a lot easier to be able to query the database for the next business date or previous month-end. And I haven't even brought up the slightly sticky situations (like vacations) or the very sticky situations (like vacations across multiple regions) or the horrendous situations (like daylight savings time across multiple regions).
The real WTF is the financial industry
Admin
What...The....F.....
This is easily the worst WTF I've seen.
Admin
Date actually works fine. You don't even have to quote the name.
Select Date
From Date
Where Date = '6/5/06'
works fine for a date table with a date column. SQL Server is smart.
Admin
Using a date table is extremely common in dimensional / analytical database design. In fact, I've never seen a dimensional database without a date table. It's a well thought-out design technique that makes certain types of queries much simpler. If you want to understand the cases where these tables make sense, see anything by Ralph Kimball:
Unfortunately, that schema doesn't look dimensional at all so the WTF might be warranted.
Admin
Patience, lad. Patience. It takes a good 10 years to become fully jaded. Eventually all the creativity and ambition get pounded out of you.
You'll know the transition is complete when you read Scott Adams' cartoons and identify more with Wally than with Dilbert.
Admin
Reading this WTF had me spitting milk out of my nose!! That and the hallway here at my office smells like vomit.
Admin
Except this was written in Access - note the relationship screen and the post from the gentleman who worked with it. I don't know about you, but I wouldn't trust my money with a bank who's critical systems are based on Access. No transaction support, so failed inserts and updates can't be rolled back. I give it 2 days before all the accounts are f**ed up.
In all the data warehouses that I designed, the business key field was called FullDate so it doesn't clash with SQL reserved words. These were not internationalized data warehouses, but if they were I might even have multiple business keys (one for each major calendar such as Julian and Hijri).
Larry
Admin
May I ask what software that was? TIA
Admin
I'm referring to the diagramming software lol...
Admin
You'd be scared to hear how many billions of dollars my current bank keeps track of using Excel spreadsheets.
Admin
The proper solution after adding the 10 years is to create a scheduled task 6/1/2016 to add more dates. Address it to [email protected].
Admin
If that's the only system that keeps track of the debits and credits, then yes I would be scared. If Excel is used for a high-level look at the business to plan intermediate loans then no. (It been awhile since I had economics, so I don't remember the technical term for bank loans obtained from the FED or other banks to cover the withdrawal percentage).
Larry
Admin
True, it's not the primary trading system, but it is the system used to report to the business profit, loss, and risk. And those numbers heavily influence the bank's trading and pricing on those deals.
Admin
Date tables are extremely useful for creatimg timeline reports and aggregating by date....
INNER JOIN tDate ON PornStartDate > tDate.CinemaxDate AND PornEndDate < tDate.CinemaxDate
gives you a listing of all of the dates a porn was on (S)Cinemax
Admin
Obviously he should have made another table for IDs. Then the system could make more Date IDs automatically, by simply adding to the ID-to-ID table!
Admin
Oops backwards .... startdate < date and enddate > date
Admin
Amen! Preach it, brother...
Admin
Ah, I was wondering who got my old job.
Admin
XML is nice, but is crap withouth a Web2.0 application written in assembler and a good OOP database with FORT scripting.
--Tei
Admin
Lev. 20:28
And if a man shall lie with such code in its sickness, and uncover its nakedness, it is an abomination: both of them shall surely be put to death, their blood SHALL be upon them.
Admin
There are reasons to make date tables, and reasons to create surrogate keys, and still today's WTF manages to be an example of how and why NOT to do either of those things ... that's what elevates it above mere stupidity.
Admin
No - it is perpetuating the WTFness. Nobody ever said anything was wrong. Several people on support can thank the developers of this for their jobs.
Of couse he will. By that time he will be primary and can say, to the new third guy, when this is brought to his attention "Maybe putting in 10 years wasn't enough...".
Actually you might be on to something here - a scheduled job every day, but only for 1 year.
Admin
Admin
I agree. All my Access databases include a table of every possible GUID for just this purpose.
--Rank
Admin
That is standard practice in a data warehouse and yes it significantly slows down inserts. Think about an order header where you have Order Date, Required Date, Ship Date, Update Date, and/or Cancel Date. For each of those date fields, you need to query the date table. Barring no other lookups, an insert in a data warehouse requires 5 selects (to get the IDs) and 1 insert whereas a normal OLTP system just requires 1 insert.
Larry
Admin
Admin
why not use a few extra megabytes and insert an extra 200 years.
Admin
Agreed - I work in a brokerage house, and many of the systems we work with need to use dates that are published by a consortium of businesses. These dates represent logical events, but have neither rhyme nor reason w.r.t. calendar dates; there's absolutely no way to program/calculate them, and you can't buy a subscription to just upload the dates because it isn't sold anywhere - all you can do is cut-n-paste the dates from the annually published list into a special-date table, and use it for lookups.
Admin
The original programmer probably have utmost three dates a year :D
Admin
This is my favorite site in the whole wide world. Having said that, I feel that I must point out that most all these WTF's are microsoft related. Perhaps the easiest tool is not the best tool?
paula.getPaula();
Admin
Admin
I've had the 'pleasure' of working on a database with a date table...I can understand the case for having such a table in complex business applications, but I don't think that particular date table held any information that you couldn't get using a DateTime object. I suppose if you REALLY want control of date formatting in your views/queries, this lets you do that. I have a feeling this pattern is more common than you think, and implemented with varying degrees of WTF-itude.
Admin
Moose wrote "paula.getPaula();".
That looks like a static factory method (in lieue of a constructor) to me, in which case it ought to be Paula.gePaula();
Picky, I know.
Admin
You'd be shocked to learn that my bank has a trading system whose blotter is Excel. Our PNL and Risk systems a bit more sane, thankfully.
Admin
This database is not normalized. This database is WTFized.
Admin
Ah, yes, the joy of naming redundancy mandated by fools... I've seen things before that made me want to just bang my head on the desk... it starts when somebody thinks it is a great idea to name things by implementation rather than purpose: tables with "TABLE" or "TBL" in the name (usually a mix of both in the same system, randomly at the start, middle or end of the name); databases such as "INFO_DB" (what-in-the-name-of-all-that-is-sacred does that tell me about the contents?) etc...
Then along come a few organic iterations of the schema; what was a table now becomes a view on the revised schema (probably called TABLE_DATA2 or TABLE_DATA_NEW [and yes: I have seen such abominations]) - but of course the old name is needed to support the legacy systems, so you now have a *view* called TABLE_DATA.
Sometimes the only fix involves breaking into the server room with either a can of petrol or a damned big magnet... I think we can be fairly sure that in such cases the backup tapes will already be redundant ;-p
Admin
A valid question, but one possible answer is: You wouldn't want to insert those dates with the IsHoliday column incorrectly set, would you? And who knows which holidays our great-grandchildren will have...
Admin
Since you0re still reading Scott Adams' catoons, I see you at least still haven't lost optimism or hope that the next one will actually be remotely funny.
Admin
Actually the screen is just as likely to have come from the 'database diagram' thingy on sql enterprise manager. However having also worked in Banking in the City Of London, you'd be surprised by just how many access and excel 'applications' exist out on those trading floors. For those guys Strategic System = Beyond My Control = Hire me a Chap Who'll sit on the Desk and make me Access excel things I make you 10 million a week Stamp fet i want on i want one i want one.
They always get their way.
Admin
so I'm not the only one sick of these stupid goggle lines...
Admin
Thanks to my kickboxing skills people never talk to me like that anymore.... Or they pay the price...
This kind of behaviour can only be caused by stupidity.
Admin
Hee hee hee.
The Integers Table
Oh dear.
Admin
It's funny.
We have to deal with these kind of issues (I work in financial services, so we have to deal with various business day rules, national holidays, dates rolling forward at times other than midnight, etc.; daylight savings we deal with by running everything in GMT year-round), yet I am hard-pressed to see the utility to such a scheme.
The big reason is that, with the exception of the holidays, the calculations are algorithmic. There may be different rules used in different situations (for example, sometimes you can roll forward past a month end, others you can't), but they are nonetheless algorithmic. So we have a centralized "working day" calulator (which owns a table of holidays) and calendar objects driven off it. Thus, we can say "given that the date is X in currency Y region Z system W, what is the next business day?".
Holidays can't be inserted 10 years in advance, because some of them float (and so are only fixed a year or two in advance), so there is an infrequent manual job to insert new ones as the need arises. But all the rest is algorithmic, so there's really no need to store *every* date.
We do, however, have the benefit of a relatively new, relatively legacy-free system; as such, calling a central "working day" service is easy.
Admin
Er, you couldn't insert 10 years if you were worried about that. Two or three, maybe.
Admin
Last time I had a problem like this, I wrote a utility for first-level support that lets them automatically add new dates by typing the numbers 4, 8, 15, 16, 23 and 42 into a console every 108 minutes...
Admin
Either an inferred member, or just having the data loading program ensure that the required dates are loaded along with the data.
It's hard to tell if it's a data warehouse or not. It sounds like a transactional system from the description, and it's not very star shaped. Maybe it was trying to be both at once.
Admin
so basicly its a key logger that store data for a longer period, hmmm......... sounds like a hackers dream who did ya say made it?
Admin
Oh, so true. A place I worked at a while ago had system configuration data in a database table, and some bright spark decided that an expiry date field would be useful, in the future. Obviously this feature was never actually used.
One day, we had a call in - some salesmen were demoing the product to a very important prospect, they'd run it through in the morning and all was good, but during the demo is failed. The problem was traced down to the sysconfig expiring around noon. After that was fixed the only question was who was to blame? We looked, and my colleague just happened to be the last person who entered that sysconfig into his database, which was then used as a "cut+paste" template for all future development dbs - including the demo system, so it had his userid stamped on all the 'updated by' columns.
So - if in doubt, get someone else to do it.
Admin
This - actually - sounds like a meta-data repository :)
Admin
Ain't that the truth.
Wankers and jumped up barrow boys, the lot of 'em. And they don't like it when you compare them unfavourably to used car salesmen.
Simon