• Frustrated Banking Programmer (unregistered)

    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

  • ChiefCrazyTalk (unregistered) in reply to pete

    What...The....F.....

    This is easily the worst WTF I've seen.

  • JR (unregistered) in reply to vomjom

    Anonymous:
    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?

    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.

  • Ryan Tomayko (unregistered)

    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.

  • (cs) in reply to Got enough wtfs of my own
    Anonymous:

    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!


    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.
  • Harsh (unregistered) in reply to Pvulmo

    Reading this WTF had me spitting milk out of my nose!!  That and the hallway here at my office smells like vomit.

  • (cs) in reply to Frustrated Banking Programmer
    Frustrated Banking Programmer:

    You know, these kinds of tables are heavily used in banking.



    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.

    Anonymous:

    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?


    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
  • BillyG (unregistered)

    May I ask what software that was? TIA

  • BillyG (unregistered) in reply to BillyG

    I'm referring to the diagramming software lol...

  • Frustrated Banking Programmer (unregistered) in reply to lpope187
    lpope187:
    Frustrated Banking Programmer:

    You know, these kinds of tables are heavily used in banking.



    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.

    You'd be scared to hear how many billions of dollars my current bank keeps track of using Excel spreadsheets.

     

  • (cs) in reply to marvin_rabbit

    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.)

    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].

     

     

  • (cs) in reply to Frustrated Banking Programmer
    Anonymous:
    You'd be scared to hear how many billions of dollars my current bank keeps track of using Excel spreadsheets.


    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
  • Frustrated Banking Programmer (unregistered) in reply to lpope187

    lpope187:
    Anonymous:
    You'd be scared to hear how many billions of dollars my current bank keeps track of using Excel spreadsheets.


    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

    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.

  • (cs) in reply to JR

    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. 

    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

  • Deparment of Redundancy Department (unregistered)

    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!

  • (cs) in reply to Bus Raker
    Bus Raker:

    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. 

    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

    Oops backwards .... startdate < date and enddate > date

  • (cs) in reply to Frustrated Banking Programmer
    Anonymous:
    The real WTF is the financial industry


    Amen!  Preach it, brother...

  • (cs) in reply to Harsh
    Anonymous:
    Reading this WTF had me spitting milk out of my nose!!  That and the hallway here at my office smells like vomit.

    Ah, I was wondering who got my old job.
  • Tei (unregistered) in reply to ParkinT
    ParkinT:
    <font size="6">Where's the XML?</font>


    XML is nice, but is crap withouth a Web2.0 application  written in assembler and a good OOP database with FORT scripting.

    --Tei
  • FundyCoder (unregistered)

    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.

  • Gnictigezoink (unregistered)

    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.

  • (cs) in reply to don
    don:
    Maybe I'm being unimaginative, but isn't putting in ten years of dates still just perpetuating the wrongness?

    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.
    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.

    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...".
    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?

    Actually you might be on to something here - a scheduled job every day, but only for 1 year.
  • Konrad (unregistered) in reply to uber1024
    Anonymous:
    How dare they build a data warehouse?!
     
    I am currently (but not for much longer) responsible for a database which does this (in a slightly more complicated fasion) as I have tables years, Weeks, Days)from 1900 to 2100) and a seperate table for minuets (this only has 1440 rows minuets are reused from day to day). I posted something about it previously
     
    I don't recall who responded to me but apperently this is standard practice in
    data Warehouse applications as it increases query performance. It is rare to
    see it in a transaction system and I suspect that it slows down inserts. I Guess
    its a question of which is more critical to the application at hand.
  • (cs) in reply to Simon Hatthon

    Simon Hatthon:
    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.

    I agree. All my Access databases include a table of every possible GUID for just this purpose.

    --Rank

  • (cs) in reply to Konrad
    Anonymous:
     
    I am currently (but not for much longer) responsible for a database which does this (in a slightly more complicated fasion) as I have tables years, Weeks, Days)from 1900 to 2100) and a seperate table for minuets (this only has 1440 rows minuets are reused from day to day). I posted something about it previously
     
    I don't recall who responded to me but apperently this is standard practice in
    data Warehouse applications as it increases query performance. It is rare to
    see it in a transaction system and I suspect that it slows down inserts. I Guess
    its a question of which is more critical to the application at hand.


    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
  • (cs) in reply to Maximilianop
    Maximilianop:
    I don´t know why... I don´t think this application will still be working 10 years from now....
    <font size="5">T</font>hat's okay.  I don't think the company will be around either.
  • anon (unregistered) in reply to triso

    why not use a few extra megabytes and insert an extra 200 years.

  • (cs) in reply to anonymous
    Anonymous:

    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. 

    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.

  • theo (unregistered)

    The original programmer probably have utmost three dates a year :D

  • moose (unregistered)

    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();

  • (cs) in reply to vomjom
    Anonymous:

    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?
    I would have to call it DATE_DT, according to my DBAs
  • (cs)

    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.

  • Asif Youcare (unregistered) in reply to moose

    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.

  • Banking Progreammer The Second (unregistered) in reply to Frustrated Banking Programmer

    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.

  • Anonymous (unregistered) in reply to Maurits

    This database is not normalized. This database is WTFized.

  • (cs) in reply to GreenLight

    GreenLight:
    Anonymous:

    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?
    I would have to call it DATE_DT, according to my DBAs

    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

  • (cs) in reply to anon
    Anonymous:
    why not use a few extra megabytes and insert an extra 200 years.


    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...
  • Mike5 (unregistered) in reply to marvin_rabbit
    marvin_rabbit:
    Anonymous:

    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!


    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.


    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.
  • piersy (unregistered) in reply to lpope187
    lpope187:
    Frustrated Banking Programmer:

    You know, these kinds of tables are heavily used in banking.



    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.

    Anonymous:

    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?


    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


    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.
  • (cs) in reply to lurker

    Anonymous:
    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..."?

     

    so I'm not the only one sick of these stupid goggle lines...

  • (cs)

    Alex Papadimoulis:

    the primary support guy thanked him and mentioned "yeah, this happened last year; we probably should have put more than a year's worth in there."

    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.

  • (cs)

    Hee hee hee.

    The Integers Table

    Oh dear.

  • (cs) in reply to Frustrated Banking Programmer

    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.

     

  • (cs) in reply to ammoQ

    ammoQ:
    Anonymous:
    why not use a few extra megabytes and insert an extra 200 years.


    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...

    Er, you couldn't insert 10 years if you were worried about that.  Two or three, maybe.

  • none given (unregistered) in reply to DrPizza

    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...

  • Richard (unregistered) in reply to lpope187

    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.


  • smithy953 (unregistered)

    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?

     

  • Andy B (unregistered) in reply to marvin_rabbit

    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.

  • Randyd (unregistered) in reply to joe bruin

    Anonymous:
    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.

    This - actually - sounds like a meta-data repository :)

  • (cs) in reply to piersy
    Anonymous:
    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.

    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

Leave a comment on “Journey to the Center of the Database”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article