• oldie (unregistered) in reply to silent d
    silent d:
    Yes, its obvious a single table with 365 columns (oops, make that 366) would have been a better design.

    But what if the number of days in a year changes? (A bit of tidal drag over the next few billion years should do it..)

  • Alpha Bette (unregistered)

    But, um, it looks like you can only store the timesheet for one employee in that table! And what do you do if they stay more than one month?

  • Old Coder (unregistered) in reply to jfruh
    jfruh:
    When you're tipping over a jug full of liquid and letting that liquid flow out, thanks to gravity, you're pouring.

    When intently studying terrible database design, you're poring.

    Actually, when studying the above design, I'd be hurling.

  • (cs) in reply to bd
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.

    As usual the TRWTF are the comments

  • Andrew (unregistered) in reply to bd
    Comment held for moderation.
  • (cs)

    So the real WTF is that the last 4 guys responsible for maintinaing the system all died bizarre and mysterious deaths?

  • Someone other than the peron who made this (unregistered)

    This person was genius. The 35 day thing amazing. WTF

  • Brady Kelly (proudly in Jo'burg) (unregistered) in reply to A Nonny Mouse
    A Nonny Mouse:
    264 columns? is it just me, or shouldn't the developer around the 20, 50, 100, 150 etc marks be thinking "there has to be a better way"
    Vertical partitioning?
  • Joe (unregistered) in reply to bd
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.

    On the other hand I must commend on the very straightforward schema design which mirrors the user interface almost perfectly. That's something you could explain even to your pointy-haired boss without his eyes glazing over.

    End users tend to think of database design as a direct one-to-one mapping of their spreadsheet designs. So when you need to design a database for someone, they submit their existing Excel solution. You, the clever DBA that you are, begin mentally dividing out tables, normalizing, finding keys, etc.

    Meanwhile, the user keeps talking about your database as if it were this one table, congruent with his Excel spreadsheet.

    It's really our job to get the requirements from the user using his spreadsheet lingo, while knowing it's not how the database will be designed. I don't blame business oriented end users for not thinking in terms of relational database design. That's not their job. It's our job to convert their model into a relational model.

    That's not a criticism of your comment, just a good segue into a point I'd like to make.

    Captcha: eros - seriously?

  • Shinobu (unregistered) in reply to iogy

    That Slashdot thread is a classic. It brought me a smile in an otherwise boring day, as did the article.

  • Joe (unregistered) in reply to Andy Goth
    Comment held for moderation.
  • Frost (unregistered) in reply to Andy Goth
    Andy Goth:
    bd:
    TRWTF is that databases don't support array fields.
    What the heck do you need array fields for in a relational database?

    That's an indication that you haven't thought about the problem enough. "Sure, let's use an array of 12 buckets for storing monthly totals." That works great until you wind up dealing with a company that uses 4-week months.

    Arrays are almost always the wrong answer in a relational DB.

  • mark (unregistered) in reply to bd

    Not that I know much about databases, but from what I remember, to do an array properly in a database, you use another table. The 'array field' becomes a unique number (for that table) that references the same number (which can now be duplicated) in the other table. Each member of the other table with the special number matching that of the original record is a value of the array (or set of values, etc).

  • Peets (unregistered) in reply to A Nonny Mouse

    No,

    that's too much to hope for. Commonly the complaint is that they need a bigger screen, and such people can also be found near the copier-printer, picking up A3 sized print jobs ..

    .. in landscape.

  • KG (unregistered) in reply to Joe
    Joe:
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.

    On the other hand I must commend on the very straightforward schema design which mirrors the user interface almost perfectly. That's something you could explain even to your pointy-haired boss without his eyes glazing over.

    End users tend to think of database design as a direct one-to-one mapping of their spreadsheet designs. So when you need to design a database for someone, they submit their existing Excel solution. You, the clever DBA that you are, begin mentally dividing out tables, normalizing, finding keys, etc.

    Meanwhile, the user keeps talking about your database as if it were this one table, congruent with his Excel spreadsheet.

    It's really our job to get the requirements from the user using his spreadsheet lingo, while knowing it's not how the database will be designed. I don't blame business oriented end users for not thinking in terms of relational database design. That's not their job. It's our job to convert their model into a relational model.

    That's not a criticism of your comment, just a good segue into a point I'd like to make.

    Captcha: eros - seriously?

    Is it really our job to allow end users to be so ignorant and think database design is exactly like spreadsheet design? Granted, you shouldn't give a lecture on set theory, but your users should at least know that if all they know are spreadsheets, they do not have the skills to properly design a database.

    I know a few people who would put "MS Access" on their resume, but know nothing about SQL and would wind up using it as a spreadsheet app.

  • silent d (unregistered) in reply to oldie
    oldie:
    silent d:
    Yes, its obvious a single table with 365 columns (oops, make that 366) would have been a better design.

    But what if the number of days in a year changes? (A bit of tidal drag over the next few billion years should do it..)

    True, could also happen if the lunar calendar was adopted (leap months, anyone?)

  • TimmyT (unregistered) in reply to bd
    Comment held for moderation.
  • (cs)

    I wish the programmer would stop pouring over the code, and start poring over it instead....

  • Top Cod3r (unregistered)

    Look, if you are new to database design you might not be familiar with the concept of denormalization, which means you make changes to the database design for performance reasons based on the types of queries your application executes. For a timesheet app, it really makes sense to do it like this. It might seem strange if you have very little experience, but trust me on this.

  • (cs) in reply to DWalker59
    DWalker59:
    I wish the programmer would stop pouring over the code, and start poring over it instead....
    I pored over it until my pores poured sweat... help poor me, por favor.
  • muttonchop (unregistered)

    The real WTF is that this timesheet won't let me record all that overtime I worked on the 36th of Undecember.

  • muttonchop (unregistered) in reply to FredSaw
    FredSaw:
    DWalker59:
    I wish the programmer would stop pouring over the code, and start poring over it instead....
    I pored over it until my pores poured sweat... help poor me, por favor.

    These puns are in pour taste.

  • Mark (unregistered) in reply to Brian B

    This just goes to show once again that if you're "programming" by copying, pasting, and change the number on the end, you're doing something very wrong._1

  • (cs) in reply to muttonchop
    the 36th of Undecember
    Record it as occurring in Smarch, instead. See fld_TS_smarch_sm.
  • Jay (unregistered) in reply to Cowards Anonymous
    Cowards Anonymous:
    T:
    A Nonny Mouse:
    264 columns? is it just me, or shouldn't the developer around the 20, 50, 100, 150 etc marks be thinking "there has to be a better way"
    He or she may have used a loop to create the columns.

    I doubt anyone capable of dreaming up a design like this had the sense of logic to create a loop. I'm thinking someone spend their morning ctr+c/ctrl+v ing..

    I suspect whoever designed this table would be more like to say, "ctrl-v? What does that mean? Sounds too complicated to me. I'll just do it the easy way, type it in myself."

    Seems to me an awful lot of bad database design is because someone said, "Normalization? Sounds complicated. Why not just do it the easy way, put everything in one table?"

  • Joe (unregistered) in reply to KG
    KG:
    Joe:
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.

    On the other hand I must commend on the very straightforward schema design which mirrors the user interface almost perfectly. That's something you could explain even to your pointy-haired boss without his eyes glazing over.

    End users tend to think of database design as a direct one-to-one mapping of their spreadsheet designs. So when you need to design a database for someone, they submit their existing Excel solution. You, the clever DBA that you are, begin mentally dividing out tables, normalizing, finding keys, etc.

    Meanwhile, the user keeps talking about your database as if it were this one table, congruent with his Excel spreadsheet.

    It's really our job to get the requirements from the user using his spreadsheet lingo, while knowing it's not how the database will be designed. I don't blame business oriented end users for not thinking in terms of relational database design. That's not their job. It's our job to convert their model into a relational model.

    That's not a criticism of your comment, just a good segue into a point I'd like to make.

    Captcha: eros - seriously?

    Is it really our job to allow end users to be so ignorant and think database design is exactly like spreadsheet design? Granted, you shouldn't give a lecture on set theory, but your users should at least know that if all they know are spreadsheets, they do not have the skills to properly design a database.

    I know a few people who would put "MS Access" on their resume, but know nothing about SQL and would wind up using it as a spreadsheet app.

    I don't care what end users think of database design. During the requirements gathering phase I'm more interesting in the requirements themselves. What is the problem that needs to be solved? If users start dictating DB design or asking me how it's designed, that's when I kick into "managing expectations" mode and gently tell them to mind their own business.

    your users should at least know that if all they know are spreadsheets, they do not have the skills to properly design a database.

    Which is why they shouldn't have any say whatsoever in the DB design. Nor should I laugh my ass off when they start telling "Here's the table you're going to create...". Of course, I still laugh, but on the inside. Can't expose their ignorance.

    Man, this site reminds me just how much I'm dieing inside every day.

  • Fister (unregistered)

    The glasses... they do NOTHING!

  • (cs) in reply to Top Cod3r
    Top Cod3r:
    Look, if you are new to database design you might not be familiar with the concept of denormalization, which means you make changes to the database design for performance reasons based on the types of queries your application executes. For a timesheet app, it really makes sense to do it like this. It might seem strange if you have very little experience, but trust me on this.
    I hope that was sarcasm. I really hope so.

    I used to have an E-R diagram that I took right out an SQL Server we had back in college, to show people how NOT to do DB design.

    The model was a multi-team project we did for a Distributed RDBMS course, which 4 out of 5 teams got right. (And maybe the fact that I cross-checked with the other 3 teams helped on that.) However the 5th team didn't quite get what normalization was! They had a table called car_accessories which had the following "fields"...

    [Power windows] varchar(255), [5-gear manual transmission] varchar(255), [A/C] varchar(255), [Luxury leather seats with electromecanic systems] varchar(255), [3.5Liter DOHC Turbocharged engine]

    ...

    I think you get my idea. Also notice how all of these are type varchar(255). Fortunately, we didn't get any bad marks as the rest of the DB model was actually good. However, I should've checked out the 5th team's "model"...

  • Frost (unregistered) in reply to Top Cod3r
    Top Cod3r:
    Look, if you are new to database design you might not be familiar with the concept of denormalization, which means you make changes to the database design for performance reasons based on the types of queries your application executes. For a timesheet app, it really makes sense to do it like this. It might seem strange if you have very little experience, but trust me on this.

    I'll pass, thanks. If you are using proper indexing, you'll be fine. If your database can't let you pick one row out of millions in a reasonbly-short time if you use the proper index, you're using the wrong database.

  • (cs) in reply to danixdefcon5
    danixdefcon5:
    Except I wouldn't think about using arrays in a relational database, as it is a 1st normal form! If you got "array" attributes, well, that's a 1:n relation, and you store that in a related table.

    Anyway, these "clever" WTF tables are usually the product of people who think that DB tables equate to SpreadSheets!

    If you store your array values in a related table, then you can no longer do a query which depends on the main table and several values in the related table. This is, btw, TRWTF with relational databases.

    For example, one has a table with the fields:

    ID, NAME, ARRAYKEY

    ARRAYKEY is the primary index on a second table:

    ARRAYID, VALUE

    Under this setup, if I want to do a query of those entries where NAME matches 'part*', ARRAY[0] is less than 3, and ARRAY[4] is greater than 5, I'm out of luck. That's because when the join happens, it creates a number of virtual records, each of which only has a single array value. Likewise, one can't check for entries where no array value is outside of a given range - one would, instead, simply get all the rows with array values within the range.

    Now, of course, there are many databases that don't have this issue. Hierarchical databases, for example, generally are just fine with multi-valued data. (Of course, hierarchical databases have other issues, including enforced data redundancy.) I hear object-oriented databases may also handle arrays ok, although I've never actually worked with one.

  • Top Cod3r (unregistered) in reply to Frost
    Comment held for moderation.
  • SQL Ninja (unregistered) in reply to tgape
    tgape:
    Now, of course, there are many databases that don't have this issue. Hierarchical databases, for example, generally are just fine with multi-valued data. (Of course, hierarchical databases have other issues, including enforced data redundancy.) I hear object-oriented databases may also handle arrays ok, although I've never actually worked with one.

    If there were a way to generate electricity from stupidity, one could power a city using only TDWTF threads that mention databases.

  • arrowdriver (unregistered) in reply to Top Cod3r
    Comment held for moderation.
  • Frank (unregistered)

    Funny, I read the article, the comments, and to me, the Everything Database thing seems to be an even bigger wtf than the table structure itself, yet no one talked about it. Multiple applications, nothing to do with each other, one database...

  • (cs) in reply to Andy Goth
    Andy Goth:
    FredSaw:
    Here you go, Alex... a little help for you.
    I find the munged italics and quotes following "Strunk and White's" to be deliciously ironic.

    But hey, it's a wiki, I can fix it. Look at this old version of the page if you want to see what I'm talking about.

    I find the "Visit the Chicago Manual of Style and Strunk and White's Elements of Style at the links below." to be TRWTF. Why go to the trouble of describing in words to the reader where to look elsewhere in the page to find a hyperlink, when a hyperlink is exactly what the situation calls for?

    davidh

  • (cs) in reply to arrowdriver
    arrowdriver:
    Top Cod3r:
    Frost:
    Top Cod3r:
    Look, if you are new to database design you might not be familiar with the concept of denormalization, which means you make changes to the database design for performance reasons based on the types of queries your application executes. For a timesheet app, it really makes sense to do it like this. It might seem strange if you have very little experience, but trust me on this.

    I'll pass, thanks. If you are using proper indexing, you'll be fine. If your database can't let you pick one row out of millions in a reasonbly-short time if you use the proper index, you're using the wrong database.

    Well, I normally use Access, but sometimes I run the upsizing wizard to upgrade to SQL Server, you know if there is a bottleneck. The nice thing is the database virtually designs itself, it won't let you make mistakes.

    Here is a link to the web page for Access if you want to find out more about it...

    http://office.microsoft.com/en-us/access/default.aspx

    Sorry Top Coder, you loose. Bad. The upsize wizard is notorious for making horrible database designs. I can't even start to counts how many tens of thousands of dollars I have made fixing people doing excatly that...

    Might want to check your sarcasm detector. I think it's broken.

  • Top Cod3r (unregistered) in reply to Lastchance
    Comment held for moderation.
  • SomeCoder (unregistered) in reply to El Duderino
    Comment held for moderation.
  • (cs) in reply to A Nonny Mouse
    A Nonny Mouse:
    264 columns? is it just me, or shouldn't the developer around the 20, 50, 100, 150 etc marks be thinking "there has to be a better way"

    i'm flabbergasted

    Nope.

    I'm studying CS and currently we're at the good ol' Turing machines.

    Our professor wants to show us how one could encode a turing machine as a string of characters. So for the states, he explains the following encoding:

    Enumerate the states, giving each of them a number. Then output the numbers in ascending order, seperated by # and terminated by ##.

    So the resulting output for a machine with four states would be: 1#2#3#4#5##

    For one with eight states: 1#2#3#4#5#6#7#8##

    Do you see the pattern?

    When one student asked why you couldn't just save the number of states, he was dumbstruck and made note to use this brilliant idea in his next book...

    Yes, before anyone complains, I know the Real WTF is to try and optimize a turing machine. Still, someone who makes his living with computational complexity should at least be aware of redundancy, should he?

  • (cs) in reply to danixdefcon5
    Tom:
    An apprentice:
    Aaargh. Why is it that always when a truly horrible atrocity shows up on this site, it involves SQL and relational design?

    Well, this one didn't involve "design" in the traditional sense.

    It doesn't involve SQL or anything relational, either. Quite a feat, really.

    MrsPost:
    I have to sob right along with the OP of the story.

    Our timesheet application doesn't support anything besides a flat table. That's right - every field in the form has to have a corresponding field in the table.

    So in very similar fashion we have fields for time in, lunch out, lunch in, etc.

    It's very sad.

    Also astonishingly rancid. What is this "application," a flat file? Can't it even manage more than one table? (Which would still be stupid, but at least manageably so.)

    danixdefcon5:
    bd:
    TRWTF is that databases don't support array fields. We all know from previous articles that every time you see bunch of variables with names containing numbers in a sequence, you can refactor all that into a single array.
    Except I wouldn't think about using arrays in a relational database, as it is a 1st normal form! If you got "array" attributes, well, that's a 1:n relation, and you store that in a related table.

    Anyway, these "clever" WTF tables are usually the product of people who think that DB tables equate to SpreadSheets!

    Which reminds me of Dorothy Parker on Katharine Hepburn: "She ran the gamut of emotions from A to B." This "database" is far, far worse than either a spreadsheet or La Hepburn -- it doesn't even get as far as "B."

  • (cs) in reply to A Nonny Mouse
    A Nonny Mouse:
    T:
    A Nonny Mouse:
    264 columns? is it just me, or shouldn't the developer around the 20, 50, 100, 150 etc marks be thinking "there has to be a better way"
    He or she may have used a loop to create the columns.

    okaaayyy.. but if they had to resort to this "loop" trickery you speak of to generate their columns, shouldn't they have been thinking "there has to be a better way"

    If the brute force approach isn't working then you're not using enough of it.

  • (cs) in reply to SoonerMatt
    SoonerMatt:
    I wish I could give everyone a screen shot of what I am working on. Anyway we have to support and app the was written in vbasic/access. It has the tblXXXX format and none of the data is normalized.

    (It's really a gem that stands out in our enterprise oracle rac setup)

    This particular article hit home on several levels.

    I'm not sure if you're knocking the "tbl" prefix for tables here, or saying that they were named numerically or something (e.g. tbl0001, tbl0002, etc.). "fld" prefixes seem pointless, but "tbl" is a good idea in this situation.

  • (cs)

    Sounds like the timesheet app I inherited... originally created by the president's son-in-law. The president's daughter is in the office beside my cube. Oh joy, oh bliss.

  • Franz Kafka (unregistered) in reply to PSWorx
    PSWorx:
    When one student asked why you couldn't just save the *number* of states, he was dumbstruck and made note to use this brilliant idea in his next book...

    Simple answer: state machines aren't guaranteed to have continuous state numbers, and using strings to name them is easier to comprehend.

  • John (unregistered) in reply to iogy
    Comment held for moderation.
  • Joe (unregistered) in reply to Frank
    Frank:
    Funny, I read the article, the comments, and to me, the Everything Database thing seems to be an even bigger wtf than the table structure itself, yet no one talked about it. Multiple applications, nothing to do with each other, one database...

    Most likely that's the result of an idiot pretending to be a DBA/developer. However, in some situations, corporate IT tells you "You only have this 1 SQL Server database to use.". Then you're forced to use it for multiple apps. That's where well chosen naming conventions make a huge difference.

  • (cs) in reply to iogy

    Well... SQL 2008 will have sparse fields in a table. There will be NO LIMIT on the number of sparse fields a row can have!

    Rejoice!

    And SQL Server is even optimizing this crab for you, since unused tables will take now room! Now you can finally implement your whole domain in one huge row!!! Isnt that great? That means you will only need a handfull of DB access methods. Thats increasing the efficency by a LOT!

  • Mr.'; Drop Database -- (unregistered) in reply to SomeCoder
    Comment held for moderation.
  • The Fake WTF (unregistered)

    So where's TABLE tblTimesheetCY that covers the entire year? That, to me, is the most obvious problem with this setup...

    Anyhow, I should probably get back to work. I need to finish this program to generate my table definition files, then research some database software to find out which allows the largest number of columns.

    We keep running out. Stupid MAX_INT. They really need to get things in gear with 128-bit computing.

  • (cs) in reply to Fister
    Fister:
    The glasses... they do NOTHING!
    Get the goggles instead.

    On second thoughts, they will do nothing too

Leave a comment on “tblTimesheet”

Log In or post as a guest

Replying to comment #:

« Return to Article