I have recently had the pleasure of dealing with this wonderful phenomena. The database of an application that I have been tasked with refactoring has audit columns on each table (created by, create date, modified by, modified date). This concept is present in many other products, and has served well in the past. The difference between those apps and this one is that all the others actually used datetime data types to store datetime information. In this app, all of the "date" columns were varchar(30). leaving me in a CAST/CONVERT hell whenever I need to query. Sorting is slow, searching is slow, coding is slow... everything is slower because of this.
Now, I agree partially with the sentiment that if you arent doing arithmetic with a number, then it should be considered as a string, but dates??? WTF is that?
Mind you, this was not the only instance of incorrectly typing. There were several instances of columns that clearly stored integer ID values (userID, appID, roleID) in varchar and char fields, leaving me to believe that either the original coder never bothered to look for other datatypes, or has a raging stiffie for varchars.
This vb.net application had some other lovelies...
- No identity keys - apparently as of 2003 when the db was created, identity keys were still not trustworthy enough to use in a real biz application. Instead, there is a separate table that stores the next available identity value for the other tables. Every time you need to insert a row, you must consult this "counter" table and then update it after the insert is successful.
- Dim [Continue] As Boolean - C'mon... Visual Studio highlights the word "Continue" as a reserved word... and that still did not clue in the original developer that maybe he shouldn't have used this as a variable name.
- every_single_database_object_has_repetitive_prefixes_and_underscores. So every table in the database will be named something like tbl_Application_, and you end up with tbl_Application_User. tbl_Application_User_Email_Address, etc. Querying the database is breaking my underscore key right off my keyboard.
- Some asshole actually used "On Error Resume Next" in this app. I would like to personally thank Microsoft for allowing this kind of bullshit in versions of vb.net after 1.0. Sure, they want to make the transition easy for VB6'ers, but this is like letting a pedophile out of jail and moving him into an apartment at Cinderella's castle.
- None of the classes have any properties, just public fields. Let me repeat that... NONE of the classes have any PROPERTIES, JUST PUBLIC FIELDS.
The best part of all of this is... I get to fix all of this, with virtually no chance of redesigning from scratch. Time to get the duct tape.
