• (cs)

    Once I had the pleasure of fixing an application where dates were stored as three TEXT fields (not VARCHAR: the actual TEXT type). One for day, one for month, one for year.

  • PlutoPlanet (unregistered) in reply to Severity One

    Once I fixed PHP based CMS, where each page had its own table. And of course: All columns were of type TEXT. This was real fun...

  • Anon (unregistered)

    A long time ago I did a database course at university. Pretty sure I remember having a mini-test once where you had to normalize a set of tables that included a date field; and the "correct" answer was to split the field into a day, month, and year field.

    I'm sure glad I didn't learn anything from my education.

  • Bill Gates (unregistered)

    600 columns ought to be enough for everyone.

  • realworldis (unregistered)

    The real WTF is the first two submitters haven't heard of OLAP

  • Doozerboy (unregistered)

    The first example seems reasonable if the database was a data warehouse used for reporting purposes.

  • Anonymous (unregistered)

    Of course there is no "No" table. The non-existence of that table signifies the boolean value "False".

  • (cs)

    TRWTF is SharePoint's use of a single table in a single database for storing the item contents of all lists and libraries of all webs of all sites of a site collection...

  • SB (unregistered) in reply to Doozerboy

    Yes, I would go so far as to say its a "good design". But only of course if "good design" is actually a metaphor meaning total sh1t.

  • Pete (unregistered)

    Date tables are pretty much unavoidable as soon as you have important properties of dates that are decided "arbitrarily" and can't be determined by any sane function, only by a lookup table.

    I.e., is a particular date a business day? To which accounting period (year/month) should transactions booked on that date go? Hint - it doesn't always equal the calendar year/month part of that date. Etc.

  • (cs) in reply to Anonymous
    Anonymous:
    Of course there is no "No" table. The non-existence of that table signifies the boolean value "False".

    I thought it signified the boolean FILE_NOT_FOUND.

  • Cowbol (unregistered)

    This kind of "stuff it all in a table and let god sort'em out!" looks like an old db2 table used with a lot of different COBOL data mapping based on 1st column. It's the kind WT... err "legacy pattern" that can lead to text data overlapping a date field because y'know we don't need this field in this case so save space.

    For extra fun, add a few (unsynchronized of course) java/hibernate mappings in the mix.

  • (cs)

    Most are WTF...but not necessarily all. I had one project (years ago) where the client was "Creative" in their definition of fiscal quarters, months, and even weeks (yes, sometimes the week ended anywhere from Thursday until Monday, Months were often 4 work weeks, but occasionally 5 - and even once 3, The fiscal quarter did not align with 3 sequential months).....There was NO other way than to use an explicit table.

  • Jim (unregistered)

    That second one looks almost identical the date table for my OLAP cube and almost the same as the one visual studio generates for you when creating a time dimension.

    I'd hate to see what a datacube would look like if Kris were to create one. I can just imagine staring at a huge drop down list of dates and times and thinking "if only i could just select 2014...."

    Number 1 is a bit WTF though. I like created and updated columns, but in a year table?

  • Farzein Lokalht (unregistered)

    When all you have is a database, everything looks like a table.

  • Fege-line (unregistered) in reply to Farzein Lokalht
    Farzein Lokalht:
    When all you have is a database, everything looks like a table.

    A wooden table.

  • (cs)

    I think this actually made me slightly ill. Ughhhhh.

  • (cs)

    the last example is PURE genus.

  • Pista (unregistered) in reply to Nagesh
    Nagesh:
    the last example is PURE genus.

    Yeah, that's the WTFest of them all.

  • Caffeine (unregistered)

    Nothing wrong with a dbo.YES table!

    That way a simple join will give you all the records you want without those pesky where clauses that just make your SQL messy.

    Should have a table for every possible filter condition you will potentially use. Saves you from those tricky homophone errors... who can ever remember whether it's where or wear or ware?

  • Dave (unregistered)

    Obviously the real WTF is the superfluous apostrophe in "it's", and the fact that none of you "geniuses" noticed it.

  • (cs) in reply to Nagesh
    Nagesh:
    the last example is PURE genus.
    Isn't it fun to notice that a Sam is so intimately connected with “Doing it wrong”?

    Just sayin'…

  • cyborg (unregistered) in reply to Bobby Tables
    Bobby Tables:
    Anonymous:
    Of course there is no "No" table. The non-existence of that table signifies the boolean value "False".

    I thought it signified the boolean FILE_NOT_FOUND.

    That is defined by a table that doesn't exist.

  • Anon (unregistered) in reply to SB
    SB:
    Yes, I would go so far as to say its a "good design". But only of course if "good design" is actually a metaphor meaning total sh1t.

    Why is there a "1" in "shit"? Are you 12?

  • Brian J. Parker (unregistered)

    I have to agree, #2 is not that unusual, there are a lot of legit reasons to use Calendar tables. On the other hand, it is odd to have a foreign key referencing it, or not to fully populate it with all possible dates from the get-go. Not quite WTF level, but odd.

  • verisimilidude (unregistered) in reply to Anon 
    "good design" is actually a metaphor meaning total sh1t.

    Why is there a "1" in "shit"? Are you 12?

    For the same reason there are peanuts in other peoples. SB obviously eats code for breakfast.

  • Panopticrat (unregistered)

    Justus's setup isn't quite as insane as it might seem at first, having encountered a practically identical record set elsewhere from a major retailer.

    This is EDI, stored in a RDBMS, in the only way that's even remotely sane, if you want to preserve it as an EDI document. It's horrendous and it sucks, and it's a shitty technology that was invented by business analysts who discovered a "fax machine" and "acid" at about the same time. But it's used by all the big boys.

  • (cs)

    Just trying to find the sense here...

    Mick's co-worker needed a way to ensure that years entered in the year field are valid (note that the column is text, not a number). After all, it might not be valid to enter 2015...so I suppose this might make sense.

    Kris's table looks like one we use internally. We have a period that looks like yyyyppp (2014001). This would be on the table as the ID, then there's a pair of co-dates that would specify '01/01/2014' to '01/31/2014'. Originally, the ID looked like FY99001, which totally explains the period ID...but anyone remember Y2K? What is it that happens when we go from FY99012 to FY00001? Right. That's why the format is now 2014001. Period tables are flexible...if you need them.

    Justus is working on a system that originated in VSAM world (or some other similar keyed flat file). Using multiple files was hard, and so one trick was to use record types to identify differing data record formats, each of which would contain data that adds to the main record. Sometimes, you could even add multiple records of the same type, such as the 410 type in this example. Of course, when we converted our payroll master VSAM file (which looked quite a bit like this) to relational database, we actually used proper relational design, but maybe their customer demanded a "straight conversion" or something. With a customer like that, this could make sense.

    The anonymous submitter's multi-column table could be highly flexible when each customer demands to store their own "specialty values", values that you don't want to have to normalize into the database. For example, one of our 30-odd customers wanted to store the date on which the employee was made "do not rehire". Now, "do not rehire" is not something you want to store in the database even as a Y/N flag, since it is a lawsuit magnet. They figured it would be "clever" to store their flag as a date labeled something weaselly like "acceptance into independence program". Yeah, right, we're not adding a normalized date column for that to our database, guys, so you can use one of the anonymous columns in the adhoc table. Of course, it would be a bad idea to use a table like this one for normal relational data, but it's fine for adhoc data, maybe that's what it's being used for here.

    Jon and Shane and Sam: WTF I give up. No possible reason.

  • Fellshard (unregistered) in reply to Doozerboy

    Bingo. The only real WTF with that one is possibly the formatting, but even that isn't a stretch if it's being sent straight to a data visualization or BI dashboard.

  • ZarroBoogs (unregistered)

    This reminds me of the time when I got to review an access database for possible integration into one of our other systems.

    The database had been programmed by a doctor who instead of using an incremental counter to assign primary key ids, he used a random number generator. I don't recall if it actually did any checks for uniqueness.

    Anyway, the RNG had no user defined bounds, so it was possible for negative ids to be assigned. Did I mention that this ID was displayed prominently to the user in the form front-end?

  • (cs) in reply to Severity One
    Severity One:
    Once I had the pleasure of fixing an application where dates were stored as three TEXT fields (not VARCHAR: the actual TEXT type). One for day, one for month, one for year.

    In my position as technical consultant, I had a design meeting with a client earlier this month in which he had done exactly that (among other stuff almost as laughable). We get used to this shit after a while, and calmly explain how we would prefer they used our product.

  • Nate (unregistered)

    Calendar tables are good things - they extremely simplify date calculations. Each date has many attributes - is it a company holiday, weekday, weekend, its equivalent in Julian, Chinese, etc? What fiscal year is it part of?

    If I want to find all sales on non-Holiday thursdays, I can do that with a calendar table easily.

  • Tom (unregistered) in reply to TheCPUWizard
    TheCPUWizard:
    Most are WTF...but not necessarily all. I had one project (years ago) where the client was "Creative" in their definition of fiscal quarters, months, and even weeks (yes, sometimes the week ended anywhere from Thursday until Monday, Months were often 4 work weeks, but occasionally 5 - and even once 3, The fiscal quarter did not align with 3 sequential months).....There was NO other way than to use an explicit table.

    That would still be a WTF table. That the WTF was caused at a different level does not suddenly make it a good thing.

  • Tharg (unregistered)

    Yet another bright shining example of why developers should never be allowed to design anything to do with a database. After all, it's just a dumping ground for objects. Come to think of it, if all we're doing is saving and retrieving objects, why don't we just write to files, save all those nasty RDBMS license fees.....

  • (cs) in reply to Tharg
    Tharg:
    Yet another bright shining example of why developers should never be allowed to design anything to do with a database. After all, it's just a dumping ground for objects. Come to think of it, if all we're doing is saving and retrieving objects, why don't we just write to files, save all those nasty RDBMS license fees.....

    ...and we know about open sores databases and all, but they're not trustworthy.

    ...right?

  • (cs)

    Larry found a DUAL table which has no counterpart SINGLE table... It has one row with one column named "DUMMY" containing a single "X"...

  • Anonymous') OR 1=1 (unregistered)

    Justus's catalog data table has a "Wooden screen silent L" in it. They clearly misspelled "Wooden table" when entering that data.

  • (cs) in reply to Pete
    Pete:
    Date tables are pretty much unavoidable as soon as you have important properties of dates that are decided "arbitrarily" and can't be determined by any sane function, only by a lookup table.

    I.e., is a particular date a business day? To which accounting period (year/month) should transactions booked on that date go? Hint - it doesn't always equal the calendar year/month part of that date. Etc.

    Yeah, fiscal calendar tables are common. Looks like the second one is just that.

    The rest are atrocious. Even as a non-DBA, I've never designed tables like that, even the first time that I touched a database. I'm assuming that the people that created these tables probably spend most of their time working with something like WordPress.

  • (cs) in reply to Severity One
    Severity One:
    Once I had the pleasure of fixing an application where dates were stored as three TEXT fields (not VARCHAR: the actual TEXT type). One for day, one for month, one for year.

    Real programmer ALWAYS store dates as numbers. This is before UTC compatible date classes came into existence. Now you have datetime and datetime2 type of columns available in MSSQL. There are so many classes that do TIMEZONEOFFSETS and thing of that nature.

    Still the simplest way of storing a date is count from Unix EPOCH and store the number in a number field. Once you do that it is simple matter of translating that number into any format you want and @CodingHorror is your uncle.

  • Uncle Al (unregistered) in reply to Pete
    Pete:
    Date tables are pretty much unavoidable as soon as you have important properties of dates that are decided "arbitrarily" and can't be determined by any sane function, only by a lookup table.

    I.e., is a particular date a business day? To which accounting period (year/month) should transactions booked on that date go? Hint - it doesn't always equal the calendar year/month part of that date. Etc.

    I've even run across organizations where a single transaction date mapped to multiple accounting periods. At that point, nothing for it but to put the accounting period into the transaction record.

  • (cs) in reply to Nagesh
    Nagesh:
    Real programmer ALWAYS store dates as numbers. This is before UTC compatible date classes came into existence. Now you have datetime and datetime2 type of columns available in MSSQL. There are so many classes that do TIMEZONEOFFSETS and thing of that nature.

    Still the simplest way of storing a date is count from Unix EPOCH and store the number in a number field. Once you do that it is simple matter of translating that number into any format you want and @CodingHorror is your uncle.

    Real programmers don't open the can of worms that is storing dates. Calculating the numbers of milliseconds from the UNIX epoch given the local time and time zone is likely a harder problem than whatever program you're writing. You would have to worry about things like the fact that Samoa skipped Friday, December 31st, 2011.

  • (cs) in reply to Jaime
    Jaime:
    Nagesh:
    Real programmer ALWAYS store dates as numbers. This is before UTC compatible date classes came into existence. Now you have datetime and datetime2 type of columns available in MSSQL. There are so many classes that do TIMEZONEOFFSETS and thing of that nature.

    Still the simplest way of storing a date is count from Unix EPOCH and store the number in a number field. Once you do that it is simple matter of translating that number into any format you want and @CodingHorror is your uncle.

    Real programmers don't open the can of worms that is storing dates. Calculating the numbers of milliseconds from the UNIX epoch given the local time and time zone is likely a harder problem than whatever program you're writing. You would have to worry about things like the fact that Samoa skipped Friday, December 31st, 2011.

    you are making my point for me.

  • (cs)

    Do real developers double-click the Submit button on a web form?

  • (cs)

    I think Mick's coworker was my predecessor... déjà varchar(50)

    Databases are to software WTFs as Australia is to biodiversity.

  • (cs)
    chubertdev:
    Do real developers double-click the Submit button on a web form?
    Real developers don't respond to Nagesh.
  • (cs) in reply to SB
    SB:
    Yes, I would go so far as to say its a "good design". But only of course if "good design" is actually a metaphor meaning total sh1t.

    You mean "good design" isn't? Isn't that like "political correctness" (which isn't!)

  • (cs) in reply to Nagesh
    Nagesh:
    Severity One:
    Once I had the pleasure of fixing an application where dates were stored as three TEXT fields (not VARCHAR: the actual TEXT type). One for day, one for month, one for year.

    Real programmer ALWAYS store dates as numbers. This is before UTC compatible date classes came into existence. Now you have datetime and datetime2 type of columns available in MSSQL. There are so many classes that do TIMEZONEOFFSETS and thing of that nature.

    Still the simplest way of storing a date is count from Unix EPOCH and store the number in a number field. Once you do that it is simple matter of translating that number into any format you want and @CodingHorror is your uncle.

    Then you need a database of leap seconds. An exercise left to the student.

  • Clockwork-Muse (unregistered)

    Agreed, Kris' table appears to be an OLAP Date.Calendar dimension, which I consider the most useful dimension table. Which is the reason I wouldn't bother with something like a Year table - it's stupid easy to derive year information from a regular Date dimension, assuming the correct columns/indices are present. You can use these things in all sorts of fun ways. For instance:

    SELECT MONTH(occurredAt) AS month, COUNT(*) AS events
FROM Log
GROUP BY MONTH(occurredAt)

    ... can't use an index, because of the scalar function. Table scan time (will be slow on large tables). However if you do something like this:

    SELECT Month.calendarMonth AS month, COUNT(*) AS events
FROM (SELECT calendarMonth, calendarDate AS start, calendarDate + 1 MONTH AS end
      FROM Calendar_Table
      WHERE dayOfMonth = 1) Month
JOIN Log
  ON Log.occurredAt >= Month.start
     AND Log.occurredAt < Month.end
GROUP BY Month.calendarMonth

    ... probably can (depends on how smart the optimizer is), because we've used a range on the join. Consider that most times aggregates consider only a small portion of the complete set (say, only one year), and this becomes even more viable.

    Not to mention cases like retail/manufacturing calendars where the fiscal periods being aggregated have no relation to the Gregorian calendar in the first place

  • Spencer (unregistered)

    TRWTF is of course Java. How do you get the year from a Java Date object? 

    getYear()
    of course...except that the docs say this method has been deprecated as of JDK v1.1 (not only that, but it returns the year minus 1900)

    Java's Calendar class seems a little better, actually having a YEAR field (in amongst the absolute mess of fields it has), but it's an abstract class requiring specific implementations to use for each culture.

    Yeah, I'll stick with .NET where DateTime has a Year property (or if I'm stuck with Java for some reason, the Joda library)

  • Charles U. Farley (unregistered)

    What I really like about Mick's database table is the update timestamp. All that data looks like it was entered in using CTRL-C, CTRL-V

Leave a comment on “Tablemania!”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article