Comment On Classic WTF: Journey to the Center of the Database

Sounds a lot like the typical "holiday" table around here...

Happy Holidays to all !!!

Not the first time Donnie was left alone at a table due to a date!

@ParkinT

Dude.... that was awful....

Actually if you've taken a masters' level class in relational design, you'd know this is how you're supposed to do it.

Sure, in a programming language you'd have a library function to convert the internal date to various display formats. But while some rudimentary functions may be available, database systems are really all about data and that's where the best optimizations are. Besides, you still haven't solved the problem of identifying holidays.

TRWTF(tm) is that the original developer didn't populate the table with 1000 years of data. I mean, the cost of a TB of disk space is less than even one support call to update the darn thing!

Also, he put another quarter into the machine.

"Relational Design", certainly not, at least if you identify proper relational design with third normal form and above as I do - but that's classic "Dimensional Design", which is Kimball's voluntarily departure from normalization.

Whether dimensional design is a gigantic WTF is another debate ...

Captcha: Nulla. Would make Chris Date blow another fuse ...

Don't forget to include a column in the date table for every possible display format. Hey, you could use the format string as the column name!

And then you need an index on every column, so you can quickly look up user entered dates and convert them to internal format! But you knew that already, right?

MidCod3r:

Actually if you've taken a masters' level class in relational design, you'd know this is how you're supposed to do it.

This database design is far from optimal. For true modularity, no table should EVER have more than two columns: A key and a value. This maximizes the ability to reduce repeated data and improve value lookup efficiency. This is how I've designed all of my old databases and none of my former bosses have ever called me in to fix anything.

TrueGenious:

none of my former bosses have ever called me in to fix anything.

The true "genious" of your story is that it sounds like you've had many many former bosses.

One comedy troll is amusing.
Many comedy trolls are tedious.

I see no problem with this as long as you are talking about some sort of data warehousing system where you may need or want a date dimension. It very much depends on the context of the system. If you told me this was OLTP I would probably have issue with it. OLAP on the other hand I wouldn't consider it a problem at first glance.

TrueGenious:

For true modularity, no table should EVER have more than two columns: A key and a value.

And, for more flexibility than offered by most data storage systems, you can pack several elements into that single data value, separated by your own custom delimiter.

(You may think I'm joking, but I worked for the VP of IS in a major retail chain, and he actually did this in a lot of his tables. Sometimes he even had subdelimited values within his delimited values. And the lusers thought he was a genious. Hence the promotion to VP. True story...)

I feel sorry for the few beginner programmers that stumble upon these forums and just might take these trolls seriously.

Trolls? Where?..
All I see are ppl poking fun at an epic WTF if this database is used in an ordinary application, and a 'maybe somewhat WTFfy' when looking at a data warehouse.

I think the term Troll is coming up too much on these boards lately...

You mean like under the seats of 194 Chem at UC Davis?

*blink*

Actually, I don't know if 194 Chem is even used anymore; haven't seen it since the late '70s. But I distinctly remember the sensation of being surrounded by regiments of lower forms of life...

CAPTCHA: Venio. Also vidio and vicio.

Mr B:

One comedy troll is amusing.
Many comedy trolls are tedious.

When I put together my team for a project, I make sure that there are at least four trolls, so that they can form a RAIT (Redundant Array of Inexpensive Trolls). That way they provide both maximum troll-uptime AND maximum troll-backups. I also ensure that the same four trolls are assigned to multiple projects, so that they can more efficiently use their spare troll-cycles.

MidCod3r:

TRWTF(tm) is that the original developer didn't populate the table with 1000 years of data.

You know what days are going to be holidays for the next 1,000 years?

MidCod3r:

Sure, in a programming language you'd have a library function to convert the internal date to various display formats. But while some rudimentary functions may be available, database systems are really all about data and that's where the best optimizations are.

How about that: Don't use the DBS for data formatting. Just fetch the data or a query and let the library functions of your favourite programming language to the rest. For the holidays add an additional table where only those are stored and perform a join on that. And some holidays don't even need some table to be stored in, as they can be derived algorithmically (Gauß invented a lot of algorithms for that).

[quote user="CaRL"][quote user="TrueGenious"](You may think I'm joking, but I worked for the VP of IS in a major retail chain, and he actually did this in a lot of his tables. Sometimes he even had subdelimited values within his delimited values. And the lusers thought he was a genious. Hence the promotion to VP. True story...)[/quote]

I've used delimited data inside a field before. Of course I was young and stupid; nowadays I'd use a separate table to store arbitrary numbers of elements associated with a key.

At least I learn from my mistakes; it's the people who don't that make me cry.

CaRL:

TrueGenious:

For true modularity, no table should EVER have more than two columns: A key and a value.

And, for more flexibility than offered by most data storage systems, you can pack several elements into that single data value, separated by your own custom delimiter.

(You may think I'm joking, but I worked for the VP of IS in a major retail chain, and he actually did this in a lot of his tables. Sometimes he even had subdelimited values within his delimited values. And the lusers thought he was a genious. Hence the promotion to VP. True story...)

We know you're not joking; that kind of "design" has already been involved in more than one WTF here. Although, subdelimiting them sounds like a heretofore unexplored idiocy.

operagost:

subdelimiting them sounds like a heretofore unexplored idiocy.

Oh, believe me, he thoroughly explored them. He was proud that he could get an entire purchase order in one record and retrieve it from the database with "just one I/O". But it was a bear when you had to insert or delete a line from the middle of the P.O. And let's not even talk about sorting it. (Brain starts to spasm, 10 years later.)

If you live in the western half of the US, and own a certain common household object, you've probably shopped there. And wondered why they were out of stock of an obviously popular item.

I swear, 99% of TheDailyWTF commenters wouldn't spot a troll if it was bullying billy goats from under a bridge... >_<

Machtyn:

I feel sorry for the few beginner programmers that stumble upon these forums and just might take these trolls seriously.

Eh...if you come to a website that's explicitly about perversions in IT, and take anything posted on it to be good real-world advice, you deserve what you get.

Someone You Know:

Eh...if you come to a website that's explicitly about perversions in IT, and take anything posted on it to be good real-world advice, you deserve what you get.

Wait, I shouldn't be getting my code snippets here?

This is a "calendar table." It's usually used when you need to know what dates are holidays. It can be further extended to mark what fiscal years dates fit into, whether a holiday is for Muslems, or any other attribute of a specific day in the universe of all dates. And yes, it has to be periodically added to, especially since those attributes can be different for future dates.

This is only a WTF if none of those needs exist.

so for first names and last names ? really? two tables? and then if they keep track of middle names? really? three tables for a list of employee names? and then they have phone numbers? really? the employee phonelist requires 4 tables?

Machtyn:

I feel sorry for the few beginner programmers that stumble upon these forums and just might take these trolls seriously.

If there's any justice in the world, the trolls will someday be charged with maintaining the code that said beginner programmers produce...

MidCod3r:

Actually if you've taken a masters' level class in relational design, you'd know this is how you're supposed to do it.

If you've gone so far as gotten a job with that master's level class (singular), You'd know that theory and practice are two separate things.

If you took that masters class at a good school (like MIT or CMU) you'd know that there is no supposed way of doing anything.

If you took more than the first master's class in 'database management for non-technical users' and had started with the undergraduate "relational algrebra" class, you would know that the natural key (attribute) would allow for all tuples access to simpler set operations without creating an alternate serogate key.

xtremezone:

I swear, 99% of TheDailyWTF commenters wouldn't spot a troll if it was bullying billy goats from under a bridge... >_<

way2trivial:

so for first names and last names ? really? two tables? and then if they keep track of middle names? really? three tables for a list of employee names? and then they have phone numbers? really? the employee phonelist requires 4 tables?

Q.E.D.

Regarding delimited data: The ASCII standard actually provides a few little-used characters for this purpose, 1C (FS: File Separator), 1D (GS: Group Separator), 1E (RS: Record Separator), and 1F (US: Unit Separator). I've actually used them sometimes for data delimiting in storage and transfer formats, but few others seem to know of them.

Mark Wilden:

This is a "calendar table." It's usually used when you need to know what dates are holidays. It can be further extended to mark what fiscal years dates fit into, whether a holiday is for Muslems, or any other attribute of a specific day in the universe of all dates. And yes, it has to be periodically added to, especially since those attributes can be different for future dates.

This is only a WTF if none of those needs exist.

That seems reasonable [disclaimer - I know next-to-nothing about DB design]. But surely then the WTF is that it's known that the table needs extending periodically and there appeared to be no documented procedure for doing so, and the support guys seemed unaware of it ?

Dan:

I see no problem with this as long as you are talking about some sort of data warehousing system where you may need or want a date dimension. It very much depends on the context of the system. If you told me this was OLTP I would probably have issue with it. OLAP on the other hand I wouldn't consider it a problem at first glance.

Looking at the other tables shown in the schema, this is clearly transactional.
Had this really been an OLAP db, this would absolutely be necessary. as far as I know, there is no other way to include a date dimension without a date table. SSAS, for example doesn't even offer a date type.

(TRWTF is appending/prepending object names with it's type)

Jeff:

Actually, I don't know if 194 Chem is even used anymore; haven't seen it since the late '70s. But I distinctly remember the sensation of being surrounded by regiments of lower forms of life...

Being a recent student of UC Davis I can verify that Chem 194 still exists, and still has an awesome underground tunnel to the Chemistry department

http://daviswiki.org/Chem_194

MidCod3r:

Actually if you've taken a masters' level class in relational design, you'd know this is how you're supposed to do it.

No kiding! I'd have aready finished my first LISP program, if it didn't take that long to define the 'increment function for up to 2147483647, and then '+, '-, '* and '/, as lambda calculus told me to. (A related problem is that 'decrement is way too slow, but I can spare a cluster...)

Anyway, don't tell anybody, but people say that some holidays are fixed in the solar calendar, while the others are fixed in the lunar calendar.

Voodoo Coder:

xtremezone:

I swear, 99% of TheDailyWTF commenters wouldn't spot a troll if it was bullying billy goats from under a bridge... >_<

way2trivial:

so for first names and last names ? really? two tables? and then if they keep track of middle names? really? three tables for a list of employee names? and then they have phone numbers? really? the employee phonelist requires 4 tables?

Q.E.D.

*applause*

Unregistered:

"Relational Design", certainly not, at least if you identify proper relational design with third normal form and above as I do - but that's classic "Dimensional Design", which is Kimball's voluntarily departure from normalization.

Whether dimensional design is a gigantic WTF is another debate ...

Captcha: Nulla. Would make Chris Date blow another fuse ...

BTFY.

Well, if we can't recognise, not one, but two excellent and relevant database puns whilst quoting a captcha, what can we recognise?

Oh look, egg-nog...

Mr B:

One comedy troll is amusing.
Many comedy trolls are tedious.

Ah, a tedium of trolls. Either a very well chosen group name for them, or the title of the next James Bond film, wherein he visits Harry Potter and snogs Bashsheba Babbling behind the bike sheds...

Voodoo Coder:

If there's any justice in the world, the trolls will someday be charged with maintaining the code that said beginner programmers produce...

Been there... and that's why now, I'm here, trying to laugh thru the pain.

So is it wrong of me to assign the beginner code maintenance to other beginners?

I actually run an accounting system that has to have it's period dates added manually. We ran into this problem a few years ago, when the guy who maintained that chunk of the system retired without mentioning that little factoid. When the table ran out of dates, it deferred it's billing to the farthest forward date it could calculate (I think it was 2025 or something).

I thought of trying to automate it, but we run a 5-4-4 billing cycle and the dates are decided arbitrarily by finance, so that's pretty impossible.

The real WTF is that dates_tb.IsHoliday isn't named DateIsHoliday.

Mr B:

One comedy troll is amusing.
Many comedy trolls are tedious.

Actually, one isn't really all that amusing.

@Satanicpussy

Probably 2038, the maximum date (03:14:07 UTC at Tuesday 19th January 2038 to be exact), because that's the maximum number of seconds from 1/1/1970 that can be stored in a 32bit signed int ;)

Dan T.:

Regarding delimited data: The ASCII standard actually provides a few little-used characters for this purpose

Sure. For use back in the 1970's and 1980's. You know, when there weren't really anything other than mainframes and 8088's?

However, time has moved on. We're almost 40 years beyond 1970 now.

Dan T.:

I've actually used them sometimes for data delimiting in storage and transfer formats, but few others seem to know of them.

I used them sometimes, too, back when I was doing C and .ASM code for DOS. They've since invented databases that hold tables, and those tables actually can hold multiple columns. For things where databases are overkill, they have something called XML that works reasonably well.

The reason that few others seem to know of them is because they haven't been commonly used in decades. Perhaps you need a refresher course in the current state of technology and data storage? ;-)

Eh...if you come to a website that's explicitly about perversions in IT, and take anything posted on it to be good real-world advice, you deserve what you get.

Finally, the first useful post! I'm printing this one out and taping it to the wall so I don't for.....wait a minute. I see what you did there. Dang.

Mark Wilden:

This is a "calendar table." It's usually used when you need to know what dates are holidays. It can be further extended to mark what fiscal years dates fit into, whether a holiday is for Muslems, or any other attribute of a specific day in the universe of all dates. And yes, it has to be periodically added to, especially since those attributes can be different for future dates.

This is only a WTF if none of those needs exist.

So why not just store a date in the dateId fields of all these tables, and then make the date the primary key of the calendar table?

This makes the specific relation unnecessary (you can always join to get the holiday, but this is unecessary for at least some of that code), shrinking the size of your DB and making inserts/updates/deletes quicker, and allows you to insert dates at any time without crashing the code, and then allowing you to add rows to the Holidays table after the fact.

DateDate

hmmmm, thank goodness for that row, otherwise it would _really_ be confusion.

You're right; that's the way it would actually be done. There are some database practitioners that don't believe in using real-world keys, and would always use an artificial "surrogate" key, but I'm not one of them.

Someone You Know:

Machtyn:

I feel sorry for the few beginner programmers that stumble upon these forums and just might take these trolls seriously.

Eh...if you come to a website that's explicitly about perversions in IT, and take anything posted on it to be good real-world advice, you deserve what you get

Some of us come for the good examples of bad examples:P

ASCII also has such terrifically under used codes as <BELL> to ring the bell on the teletype, <LF> to return the print head carriage back to the beginning of the line, and cool and useful winners like <VT> vertical tab.

But really, is it not best to simply serialize objects into xml and store them in the DB? Heck with Xpath and XQuery, I can intermix SQL and and XML for the ultimate in ease of use and maintainability!

(ok..sarcasm, yes. Troll no.)