• (cs)

    I have a really funny feeling right about now....

    About five years ago I joined a company that used a very similar approach except that they used an access database per customer. When they made the switch to SQL server this policy continued - I have a very hazy memory of writing some code that would go off an create a brand new database whenever a new client order appeared.

    Given the non British date formatting I think I'm off the hook :-)

     

  • (cs)

    "<!--StartFragment --> No - no words. No words to describe it. Poetry! They should've sent a poet." - Ellie from the movie Contact.[:|]

  • (cs)

    Just what would you geniuses have done?  Let the data from one customer get all mixed together with the other customers'?  Don't be rediculous!  How would you ever separate the data again?  You wouldn't take every file on your computer and store it in one giantic folder, right?

  • (cs)

    That's simply beautiful database management there. I applaud whoever did that to keep things as organized as possible. Now when you're searching for something, you only have to search a few records as long as you know the date it happened. If you don't know the date though, you're kinda screwed. Maybe the next SQL release from Microsoft will include support for a date field, eh?

    And lone: remind me never to hire you for any programming tasks. Do you really not see the problem separating every customer out into their own databases? The concept of databases allows you to put everyone in the same table (because your company will most likely need the same information from each customer, and will provide similar services to them all), or use separate tables within a database so each of them can access shared resources within the database.

    And to counter your analogy, would you want to keep each file on a separate computer or centralize them all on one system?

  • (cs)

    Ohh. Ha Ha I get it!

    April Fools [;)]

    The clue:

    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")
     strDatabaseName = Right$(Format$(ReleaseDate, "mm/dd/yy"), 2) _
     & Left$(Format$(ReleaseDate, "mm/dd/yy"), 2) & _
     Mid$(Format$(ReleaseDate, "mm/dd/yy"), 4, 2)

    No monkey in its right mind would do something that inefficiently.. Oh. Wait this is The Daily WTF... [:$]

    Unbelievable.. My eyes are burning [:'(]

  • Worf (unregistered) in reply to mahoneyr

    I actually designed a database exactly like you described, in Access.

    It was used to keep track of the various drawings (blueprints, CAD files) produced. There was a new Access database per project (not too unreasonable - these projects tended to last many months), but it kept track of such items as what drawings were sent (and being able to generate the transmittal forms helped greatly with this - just punch up what documents you wanted to send out, hit "Generate" and a form suitable for sending would appear on the screen for you to print out, plus update all the individual drawings' event tracking records to say this as well.

    So sometimes, you need multiple databases to keep everything all separated from each other.

    I have no idea if they're still using this today - this was done nearly a decade ago...

  • Neil (unregistered) in reply to skicow

    and people wonder why VB6 coders got such a bad rap.

  • Daveh (unregistered) in reply to Worf

    So sometimes, you need multiple databases to keep everything all separated from each other.

    To a certain extent this is true, but I always considered that using a different database was the very top level of organization, reserved for data that is not related and not ever will be related.

    For project example I might have just added a "Project" field and just used 'Select * FROM SomeTable WHERE (Project="001")', though using multiple databases in Access may help performance if things get big.

  • (cs) in reply to Mike R

    Mike R:

    Unbelievable.. My eyes are burning [:'(]

    I think that it's quite believable that your eyes are burning after looking at this code! [;)]

  • (cs) in reply to Manni
    Manni:

    And lone: remind me never to hire you for any programming tasks. Do you really not see the problem separating every customer out into their own databases? The concept of databases allows you to put everyone in the same table (because your company will most likely need the same information from each customer, and will provide similar services to them all), or use separate tables within a database so each of them can access shared resources within the database.

    I think that lone was being sarcastic....but I could be wrong since it's sometimes hard to distinguish in some posts on this site.

  • None (unregistered)

    I have to admit that I, too, did something like this once. My excuse, however, is that this was my during my first summer job while I was still in highschool and had just finished my first real programming class. I write much better code now, I swear.

  • (cs) in reply to skicow

    OK, bear with me one this one:  Actually, to me, the VB code looks fine.  I mean, in terms of the code itself, the formatting, structure, variable usuage, declarations, proper clean up of resources -- it looks quite fine at a quick first glance.

    The problem is, of course, the entire logical and physical design of the entire system.  This is a great example of where sometimes even the code itself might be fine, but if you have a crappy logical design, it doesn't matter how good of a coder you are, you will have an inefficient mess of a system. 

    All too often I see people design databases where there's one table per customer or things like that and I think to myself: why do people insist on making things more complicated than they need to be?  I guess it all comes down to those "highly paid" hourly consultants again!

  • (cs) in reply to Jeff S

    LoneProgrammer was defintely joking, guys.  A good example of how it is easier to determine the intent of someone who is not anonymous since you get a chance to be familiar with their style and knowledge.

  • (cs) in reply to Jeff S

    [:O] Wow, oh, Wow.  I must now purge the evil from my computer and shred the hard drive and rebuild. [:S]

  • (cs) in reply to Mike R

    Mike R:
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")
     strDatabaseName = Right$(Format$(ReleaseDate, "mm/dd/yy"), 2) _
     & Left$(Format$(ReleaseDate, "mm/dd/yy"), 2) & _
     Mid$(Format$(ReleaseDate, "mm/dd/yy"), 4, 2)
    Dayum, that's some mighty fine WTF right there!

    Ok, just ignore the fact for now that he could have simply done
    strDatabaseName = Format$(ReleaseDate, "yymmdd")

    The REAL wtf is
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")

    Taking a date variable, formatting it as a string, then implicitly casting it back to a date.
    Ok, now that I think about it, this would eliminate any "time" component of a date/time variable, but I HIGHLY doubt that is the reason for that line.

  • Driver of the short bus (unregistered)

    And since it runs as "sa", there is only one password to remember for the whole system ;-)

  • (cs) in reply to Manni
    Manni:

    And lone: remind me never to hire you for any programming tasks. Do you really not see the problem separating every customer out into their own databases?



    Nope!  I'm really just that dumb.   durrrr....

    **drool**

  • (cs) in reply to BradC
    BradC:

    Mike R:
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")
     strDatabaseName = Right$(Format$(ReleaseDate, "mm/dd/yy"), 2) _
     & Left$(Format$(ReleaseDate, "mm/dd/yy"), 2) & _
     Mid$(Format$(ReleaseDate, "mm/dd/yy"), 4, 2)
    Dayum, that's some mighty fine WTF right there!

    Ok, just ignore the fact for now that he could have simply done
    strDatabaseName = Format$(ReleaseDate, "yymmdd")

    The REAL wtf is
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")

    Taking a date variable, formatting it as a string, then implicitly casting it back to a date.
    Ok, now that I think about it, this would eliminate any "time" component of a date/time variable, but I HIGHLY doubt that is the reason for that line.


    I'll bet you anything that code grew out of the original coder's frustration over releasedate not being formatted correctly - he figures, hey, I just formatted it and redeclared the variable as a string! So he uses left, etc, and gets totally wrong results every time, so he just copies the above statement inside each and HEY IT WORKS so he's not going to touch it, right? (Alternately, he started that way and "refactored" to only compute it once, but it didn't work, so he left this half-there.) This is deep juju for someone who doesn't grasp the concept of strong typing.

    This is very useful in huge-scale access databases (created by cheap managers) that slow to a crawl and bloat up with more data, where you're absolutely certain that you'll never or extremely rarely need data from >1 database. Doesn't make it any less of a wtf, and in sql server (or even sqlite!) there's no such excuse.
  • (cs) in reply to BradC
    BradC:

    Mike R:
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")
     strDatabaseName = Right$(Format$(ReleaseDate, "mm/dd/yy"), 2) _
     & Left$(Format$(ReleaseDate, "mm/dd/yy"), 2) & _
     Mid$(Format$(ReleaseDate, "mm/dd/yy"), 4, 2)
    Dayum, that's some mighty fine WTF right there!

    Ok, just ignore the fact for now that he could have simply done
    strDatabaseName = Format$(ReleaseDate, "yymmdd")

    The REAL wtf is
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")

    Taking a date variable, formatting it as a string, then implicitly casting it back to a date.
    Ok, now that I think about it, this would eliminate any "time" component of a date/time variable, but I HIGHLY doubt that is the reason for that line.


    I'll bet you anything that code grew out of the original coder's frustration over releasedate not being formatted correctly - he figures, hey, I just formatted it and redeclared the variable as a string! So he uses left, etc, and gets totally wrong results every time, so he just copies the above statement inside each and HEY IT WORKS so he's not going to touch it, right? (Alternately, he started that way and "refactored" to only compute it once, but it didn't work, so he left this half-there.) This is deep juju for someone who doesn't grasp the concept of strong typing.

    This is very useful in huge-scale access databases (created by cheap managers) that slow to a crawl and bloat up with more data, where you're absolutely certain that you'll never or extremely rarely need data from >1 database. Doesn't make it any less of a wtf, and in sql server (or even sqlite!) there's no such excuse.
  • (cs) in reply to foxyshadis

    Sorry, my flaky net connection's fault. >_>

  • (cs)

    <FONT face=Arial size=2>God, even at 11:45 PM this is still the best damn site! As for the code (or more accurately, system design) there might be reasons for multple db's per system (as one or two people have pointed out when maybe using access), but I swear, if I ever meet somebody that does something like described here (a db per day!) in person, I'm gouging out his/hers eyes. No excuses! Damn, that's just nasty! [8o|]</FONT>

    <FONT face=Arial size=2>365 DBs a year, guess a system life time of 3-4 year min. That makes 1095 - 1460 db's for the life time! Gotta be a bitch draw report data for say, the last six months!</FONT> 

  • tSQL (unregistered)

    <FONT face=Arial size=2>I work with systems like this and they are good systems when they properly meet business and technical requirements.  Usually read-only cyclical data falls into this design.  Multiple DBs allows for </FONT>

    • <FONT face=Arial size=2>Old cycles to be retired by simply detaching/archiving the database and removing the connection string from the library.  </FONT>
    • <FONT face=Arial size=2>Databases can also be be hosted on multiple servers for both load balancing and fail over benefits.</FONT>

    <FONT face=Arial size=2>There are benefits.  Northwind developers need to get over the initial "wtf" shock when first exposed to these systems.  Dig in - figure it out.  Then when you realize that each DB is ONLY storing one record containing a path to a flat file [c:\data\20050401_wtf.dat] and this flat file truly contains your customer's data.... then do I have the post for you!</FONT>

    <FONT face=Arial></FONT> 

    <FONT face=Arial></FONT> 

  • (cs) in reply to loneprogrammer
    loneprogrammer:
    Manni:

    And lone: remind me never to hire you for any programming tasks. Do you really not see the problem separating every customer out into their own databases?



    Nope!  I'm really just that dumb.   durrrr....

    **drool**

    Well excuse the sh*t out of me. I deeply apologize for insulting your intelligence by assuming you were serious. After all, this forum never has people defending the worst code imaginable. And your three lines of text didn't drip with sarcasm the way some more obvious posters do. If only there were some way in this text-based environment to denote when someone is being facetious to avoid confusion.</sarcasm>

  • (cs)

    How about backups.
    I guess they would have to create and schedule a new backup everytime a database is created.
    I don't know if SQL Server will queue a backup if they overlap, But if you did have to schedule the backups at different times then there are only 1440 minutes in the day. 
    So after 4 years this system will be backing itself up every minute, assuming they do daily backups, and with all the thought put into the design of this system, I'd have to assume they do.

  • (cs) in reply to Manni

    I wasn't insulted at all.  Think nothing of it.

  • (cs) in reply to Sweets

    Sweets:
    How about backups.
    I guess they would have to create and schedule a new backup everytime a database is created.

    If each database is a day's data, they probably only need to back it up once, ever. 

    Granted, one per day seems a bit excessive, but this sort of approach has its place.  We just replaced a billing system that used a separate database for the phone calls for each bill run with one that keeps them all in one table in the same database as all the customer information.  Retiring old data is now more complicated, and we have to deal with 100 gig of crap we don't want whenever we use a backup to set up a test environment.  It is, of course, easier to query across multiple bill runs, but it doesn't seem to be a gain, overall.

  • Codewulf at home (unregistered) in reply to PstScrpt

    Where I work we have a database per job.  A job may last 6 months to 10 years.  When we were not on sql, the database was archived with the other files when the job was closed.  Now, nothing has been archived for years thus the number of databases are many and growing weekly.[*-)]

  • (cs) in reply to Codewulf at home

    Anybody notice that the passwords used in the connectionstring are stored in the 'connection' database in unencrpyted form. If you are going to go to all those length to be able to have different passwords (for security reasons?) then I'd guess you'd want to encrypt them too. But these people don't. [:O]

    Drak

  • Abby Normal (unregistered) in reply to Tallies

    Last 6 months report?  Why that's easy.  We have a cron job that kicks off every night that pulls all the reportable data from each day of the last 6 months into the rolling 6 month report database? [;)]

    This is an abomination to all that is right and decent.

  • (cs) in reply to Abby Normal

    You people don't get it. The real WTF here is that the date format for each database isn't Y2K compliant! [:p]

    </sarcasm>

  • (cs) in reply to tSQL
    Anonymous:

    <font face="Arial" size="2">I work with systems like this and they are good systems when they properly meet business and technical requirements.  Usually read-only cyclical data falls into this design.  Multiple DBs allows for </font>

    • <font face="Arial" size="2">Old cycles to be retired by simply detaching/archiving the database and removing the connection string from the library.  </font>
    • <font face="Arial" size="2">Databases can also be be hosted on multiple servers for both load balancing and fail over benefits.</font>

    <font face="Arial" size="2">There are benefits.  Northwind developers need to get over the initial "wtf" shock when first exposed to these systems.  Dig in - figure it out.  Then when you realize that each DB is ONLY storing one record containing a path to a flat file [c:\data\20050401_wtf.dat] and this flat file truly contains your customer's data.... then do I have the post for you!</font>

     


    You only need to read this site more often to realize that a lot of times, it's the business and technical requirements that are the WTF! Here's a perfect example: http://thedailywtf.com/forums/31551/ShowPost.asp

  • Spidey (unregistered) in reply to Jon Limjap

    Jon Limjap:
    You people don't get it. The real WTF here is that the date format for each database isn't Y2K compliant! [:p]

    </SARCASM>(/sarcasm)


    I love this stuff. All you yanks needing the sarcasm pointed out! Brilliant.

    Why not have another set of tags like the following:
    (/rant)
    (/nitpicking)
    (/petulant reply)
    or even
    (/Awooga! Awooga! Joke coming up!)

    Oh, wait. I forgot one, that everyone will now need:
    (/Indignant American pissed (off) at Brit)

  • hakan (unregistered)

    This is the most bizarre implementation I've ever seen. Who's the idiot that designed this piece? I wonder where he/she got the idea. VB6 for dummies?

  • (cs) in reply to Manni

    You should really consider using an open tag for the sarcasm.  That way we can tell if any part of the post is serious.  [:D]

  • (cs)

    A crappy design like this could have it's advantages, of course. Basically, it means the DB administrator can just drop one or more databases and thus with them all the information from a certain period. No need to trouble the users with maintenance of all the data. [:D]

    I also wonder if this approach has some performance gains, as long as you don't need any actions involving multiple databases. Technically, you're dealing with lots of small databases here and most of them will probably stay unmodified for a long time after they have been filled on a certain day. If everything would be in a single database, you'd have a huge amount of data in your database that would require SQL Server to optimize it's indices once in a while. But now, those old databases are only accessed when people need old data and once SQL Server has optimized the indices for these databases, it doesn't have to optimize them ever again. With a single, growing database those optimizations could cost some valuable server-time.

    So a performance gain in return for a crappy design? Well, if it works, it's worth the trouble. It it fails to work, start whining because you're in deep, deep s***...

  • (cs) in reply to Spidey

    Anonymous:
    Why not have another set of tags like the following:
    (/rant)
    (/nitpicking)
    (/petulant reply)

    How geeky is it of me that the thing that really bothered me about Jon's message was the lack of opening "(sarcasm)" tag.

  • Bastian (unregistered) in reply to Sweets

    What about some really kludgey sort of version control? 

    You could create a daily snapshot of the database, and if you realize you need to rollback a change that somebody made, you look at the DB snapshot for some known date and change the offending rows to their state on that day. 

    Apparently not what is happening here, and I'd say it's a WTF, too, but I've heard someone suggest such a setup before.

  • (cs) in reply to JamesCurran
    JamesCurran:

    Anonymous:
    Why not have another set of tags like the following:
    (/rant)
    (/nitpicking)
    (/petulant reply)

    How geeky is it of me that the thing that really bothered me about Jon's message was the lack of opening "(sarcasm)" tag.



    Answer: Pretty damned geeky.

    Nothing to be ashamed of, though :)

    -blue

  • (cs) in reply to Blue

    <sarcasm>
    To hell with opening tags!
    </sarcasm>

    [:p]

  • (cs) in reply to mahoneyr

    I have actually seen that before as well, though not quite as bad.  Where I work, one of our old products could store historical positional data for a number of different vehicles, with about 1 position a minute, and store it in a Paradox database.  Obviously, the data would build up, and Paradox didn't handle large tables very well, we created one table for each vehicle.  This helped, but the data would still build up over time so after a certain period of time (I think it was a month) the table would be renamed and a new table would be created.

  • tim (unregistered) in reply to Jon Limjap

    <sarcasm>
    <troll>
    Well formed XML is for sissies.
    </sarcasm>
    </troll>

  • Pollo Frito (unregistered) in reply to Worf
    Worf:
    I actually designed a database exactly like you described
    Oh dear
    Worf:
    , in Access.
    Oh dear oh dear

    ... and yes I did read the rest of your post and ...

    Oh dear

  • Pollo Frito (unregistered) in reply to tim
    tim:
    <sarcasm> <troll> Well formed XML is for sissies. </troll> </sarcasm>

    FTFY - incorrect nesting...

  • Pollo Frito (unregistered) in reply to tim
    tim:
    <sarcasm> <troll> Well formed XML is for sissies. </troll> </sarcasm>

    FTFY - incorrect nesting

    Catcha: Populus, genus Populus (a genus of trees of the family Salicaceae that is found in the northern hemisphere; poplars)

  • Pollo Frito (unregistered) in reply to tim
    tim:
    <sarcasm> <troll> Well formed XML is for sissies. </troll> </sarcasm>

    FTFY - incorrect nesting

    Catcha: Populus, genus Populus (a genus of trees of the family Salicaceae that is found in the northern hemisphere; poplars)

Leave a comment on “A Database's Database”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article