Not too long ago, Erik was hired to do extend and maintain a timesheet application for an employment agency. It was an ASP/VBScript web-app with data that lived in the Everything Database: a single SQL Server database filled with random tables from all sorts of various applications.

After paging through the tables, Erik found a few that seemed to be for the timesheet application. The first one he opened up was tblTimesheet. 

TABLE tblTimesheet
(
  fld_TS_TimesheetPK BIGINT NOT NULL,
  fld_TS_Created DATETIME NOT NULL,
  fld_TS_ContractPK BIGINT NOT NULL,
  fld_TS_TsTemplate INT NOT NULL,
  fld_TS_Modified DATETIME NOT NULL,
  fld_TS_status INT NOT NULL,
  fld_TS_freetext VARCHAR(1000) NULL,
  fld_TS_standby SMALLDATETIME NULL,
  fld_TS_callout SMALLDATETIME NULL,
  fld_TS_we SMALLDATETIME NOT NULL,
  fld_TS_d1s SMALLDATETIME NULL,
  fld_TS_d2s SMALLDATETIME NULL,
  fld_TS_d3s SMALLDATETIME NULL,
  fld_TS_d4s SMALLDATETIME NULL,
  fld_TS_d5s SMALLDATETIME NULL,
  fld_TS_d6s SMALLDATETIME NULL,
  fld_TS_d7s SMALLDATETIME NULL,
  fld_TS_d1e SMALLDATETIME NULL,
  fld_TS_d2e SMALLDATETIME NULL,
  fld_TS_d3e SMALLDATETIME NULL,
  fld_TS_d4e SMALLDATETIME NULL,
  fld_TS_d5e SMALLDATETIME NULL,
  fld_TS_d6e SMALLDATETIME NULL,
  fld_TS_d7e SMALLDATETIME NULL,
  fld_TS_d1b SMALLDATETIME NULL,
  fld_TS_d2b SMALLDATETIME NULL,
  fld_TS_d3b SMALLDATETIME NULL,
  fld_TS_d4b SMALLDATETIME NULL,
  fld_TS_d5b SMALLDATETIME NULL,
  fld_TS_d6b SMALLDATETIME NULL,
  fld_TS_d7b SMALLDATETIME NULL,
  fld_TS_d1t SMALLDATETIME NULL,
  fld_TS_d2t SMALLDATETIME NULL,
  fld_TS_d3t SMALLDATETIME NULL,
  fld_TS_d4t SMALLDATETIME NULL,
  fld_TS_d5t SMALLDATETIME NULL,
  fld_TS_d6t SMALLDATETIME NULL,
  fld_TS_d7t SMALLDATETIME NULL,
  fld_TS_d1OT1 SMALLDATETIME NULL,
  fld_TS_d2OT1 SMALLDATETIME NULL,
  fld_TS_d3OT1 SMALLDATETIME NULL,
  fld_TS_d4OT1 SMALLDATETIME NULL,
  fld_TS_d5OT1 SMALLDATETIME NULL,
  fld_TS_d6OT1 SMALLDATETIME NULL,
  fld_TS_d7OT1 SMALLDATETIME NULL,
  fld_TS_d1OT2 SMALLDATETIME NULL,
  fld_TS_d2OT2 SMALLDATETIME NULL,
  fld_TS_d3OT2 SMALLDATETIME NULL,
  fld_TS_d4OT2 SMALLDATETIME NULL,
  fld_TS_d5OT2 SMALLDATETIME NULL,
  fld_TS_d6OT2 SMALLDATETIME NULL,
  fld_TS_d7OT2 SMALLDATETIME NULL,
  fld_TS_d1sd VARCHAR(15) NULL,
  fld_TS_d2sd VARCHAR(15) NULL,
  fld_TS_d3sd VARCHAR(15) NULL,
  fld_TS_d4sd VARCHAR(15) NULL,
  fld_TS_d5sd VARCHAR(15) NULL,
  fld_TS_d6sd VARCHAR(15) NULL,
  fld_TS_d7sd VARCHAR(15) NULL,
  fld_TS_totalt DECIMAL(18, 6) NULL,
  fld_TS_totalOT1 DECIMAL(18, 6) NULL,
  fld_TS_totalOT2 DECIMAL(18, 6) NULL 
)

While Erik certainly appreciated the self-documenting "fld_*" prefixes -- you never know, they might decide to start putting cats (ktty_*) or Richard Grieco (dchbg_*) in database tables, too -- he wasn't quite sure what columns like "fld_TS_d3e" were all about. After half an hour of pouring over similarly documented code, Erik finally figured it out: each row represented a week with the start time (those ending in s), end time (e), length of break (b) and total working time (t - calculated when timesheet is saved) recorded on a per-day basis in each column. He figured that the overtime fields (those ending in OT1 and OT2) and the Special day (sd) were been added at a later date when requirements changed.

Digging further into the system, Erik came across the the "tblTimesheetCM" table. It seemed to have the same format as the weekly table, but recorded time information over the month... 

TABLE tblTimesheetCM
(
  fld_TS_TimesheetPK BIGINT NOT NULL,
  fld_TS_Created DATETIME NOT NULL,
  fld_TS_ContractPK BIGINT NOT NULL,
  fld_TS_TsTemplate INT NOT NULL,
  fld_TS_Modified DATETIME NOT NULL,
  fld_TS_status INT NOT NULL,
  fld_TS_freetext VARCHAR(1000) NOT NULL,
  fld_TS_we SMALLDATETIME NOT NULL,
  fld_TS_d1s SMALLDATETIME NULL,
  fld_TS_d2s SMALLDATETIME NULL,
  fld_TS_d3s SMALLDATETIME NULL,
  fld_TS_d4s SMALLDATETIME NULL,
  fld_TS_d5s SMALLDATETIME NULL,
  fld_TS_d6s SMALLDATETIME NULL,
  fld_TS_d7s SMALLDATETIME NULL,
  fld_TS_d1e SMALLDATETIME NULL,
  fld_TS_d2e SMALLDATETIME NULL,
  fld_TS_d3e SMALLDATETIME NULL,
  fld_TS_d4e SMALLDATETIME NULL,
  fld_TS_d5e SMALLDATETIME NULL,
  fld_TS_d6e SMALLDATETIME NULL,
  fld_TS_d7e SMALLDATETIME NULL,
  fld_TS_d1b SMALLDATETIME NULL,
  fld_TS_d2b SMALLDATETIME NULL,
  fld_TS_d3b SMALLDATETIME NULL,
  fld_TS_d4b SMALLDATETIME NULL,
  fld_TS_d5b SMALLDATETIME NULL,
  fld_TS_d6b SMALLDATETIME NULL,
  fld_TS_d7b SMALLDATETIME NULL,
  fld_TS_d1t SMALLDATETIME NULL,
  fld_TS_d2t SMALLDATETIME NULL,
  fld_TS_d3t SMALLDATETIME NULL,
  fld_TS_d4t SMALLDATETIME NULL,
  fld_TS_d5t SMALLDATETIME NULL,
  fld_TS_d6t SMALLDATETIME NULL,
  fld_TS_d7t SMALLDATETIME NULL,
  fld_TS_d1OT1 SMALLDATETIME NULL,
  fld_TS_d2OT1 SMALLDATETIME NULL,
  fld_TS_d3OT1 SMALLDATETIME NULL,
  fld_TS_d4OT1 SMALLDATETIME NULL,
  fld_TS_d5OT1 SMALLDATETIME NULL,
  fld_TS_d6OT1 SMALLDATETIME NULL,
  fld_TS_d7OT1 SMALLDATETIME NULL,
  fld_TS_d1tt SMALLDATETIME NULL,
  fld_TS_d2tt SMALLDATETIME NULL,
  fld_TS_d3tt SMALLDATETIME NULL,
  fld_TS_d4tt SMALLDATETIME NULL,
  fld_TS_d5tt SMALLDATETIME NULL,
  fld_TS_d6tt SMALLDATETIME NULL,
  fld_TS_d7tt SMALLDATETIME NULL,
  fld_TS_wk1comm VARCHAR(50) NULL,
  fld_TS_wk1total DECIMAL(18, 6) NULL,
  fld_TS_d8s SMALLDATETIME NULL,
  fld_TS_d9s SMALLDATETIME NULL,
  fld_TS_d10s SMALLDATETIME NULL,
  fld_TS_d11s SMALLDATETIME NULL,
  fld_TS_d12s SMALLDATETIME NULL,
  fld_TS_d13s SMALLDATETIME NULL,
  fld_TS_d14s SMALLDATETIME NULL,
  fld_TS_d8e SMALLDATETIME NULL,
  fld_TS_d9e SMALLDATETIME NULL,
  fld_TS_d10e SMALLDATETIME NULL,
  fld_TS_d11e SMALLDATETIME NULL,
  fld_TS_d12e SMALLDATETIME NULL,
  fld_TS_d13e SMALLDATETIME NULL,
  fld_TS_d14e SMALLDATETIME NULL,
  fld_TS_d8b SMALLDATETIME NULL,
  fld_TS_d9b SMALLDATETIME NULL,
  fld_TS_d10b SMALLDATETIME NULL,
  fld_TS_d11b SMALLDATETIME NULL,
  fld_TS_d12b SMALLDATETIME NULL,
  fld_TS_d13b SMALLDATETIME NULL,
  fld_TS_d14b SMALLDATETIME NULL,
  fld_TS_d8t SMALLDATETIME NULL,
  fld_TS_d9t SMALLDATETIME NULL,
  fld_TS_d10t SMALLDATETIME NULL,
  fld_TS_d11t SMALLDATETIME NULL,
  fld_TS_d12t SMALLDATETIME NULL,
  fld_TS_d13t SMALLDATETIME NULL,
  fld_TS_d14t SMALLDATETIME NULL,
  fld_TS_d8OT1 SMALLDATETIME NULL,
  fld_TS_d9OT1 SMALLDATETIME NULL,
  fld_TS_d10OT1 SMALLDATETIME NULL,
  fld_TS_d11OT1 SMALLDATETIME NULL,
  fld_TS_d12OT1 SMALLDATETIME NULL,
  fld_TS_d13OT1 SMALLDATETIME NULL,
  fld_TS_d14OT1 SMALLDATETIME NULL,
  fld_TS_d8tt SMALLDATETIME NULL,
  fld_TS_d9tt SMALLDATETIME NULL,
  fld_TS_d10tt SMALLDATETIME NULL,
  fld_TS_d11tt SMALLDATETIME NULL,
  fld_TS_d12tt SMALLDATETIME NULL,
  fld_TS_d13tt SMALLDATETIME NULL,
  fld_TS_d14tt SMALLDATETIME NULL,
  fld_TS_wk2comm VARCHAR(50) NULL,
  fld_TS_wk2total DECIMAL(18, 6) NULL,
  fld_TS_d15s SMALLDATETIME NULL,
  fld_TS_d16s SMALLDATETIME NULL,
  fld_TS_d17s SMALLDATETIME NULL,
  fld_TS_d18s SMALLDATETIME NULL,
  fld_TS_d19s SMALLDATETIME NULL,
  fld_TS_d20s SMALLDATETIME NULL,
  fld_TS_d21s SMALLDATETIME NULL,
  fld_TS_d15e SMALLDATETIME NULL,
  fld_TS_d16e SMALLDATETIME NULL,
  fld_TS_d17e SMALLDATETIME NULL,
  fld_TS_d18e SMALLDATETIME NULL,
  fld_TS_d19e SMALLDATETIME NULL,
  fld_TS_d20e SMALLDATETIME NULL,
  fld_TS_d21e SMALLDATETIME NULL,
  fld_TS_d15b SMALLDATETIME NULL,
  fld_TS_d16b SMALLDATETIME NULL,
  fld_TS_d17b SMALLDATETIME NULL,
  fld_TS_d18b SMALLDATETIME NULL,
  fld_TS_d19b SMALLDATETIME NULL,
  fld_TS_d20b SMALLDATETIME NULL,
  fld_TS_d21b SMALLDATETIME NULL,
  fld_TS_d15t SMALLDATETIME NULL,
  fld_TS_d16t SMALLDATETIME NULL,
  fld_TS_d17t SMALLDATETIME NULL,
  fld_TS_d18t SMALLDATETIME NULL,
  fld_TS_d19t SMALLDATETIME NULL,
  fld_TS_d20t SMALLDATETIME NULL,
  fld_TS_d21t SMALLDATETIME NULL,
  fld_TS_d15OT1 SMALLDATETIME NULL,
  fld_TS_d16OT1 SMALLDATETIME NULL,
  fld_TS_d17OT1 SMALLDATETIME NULL,
  fld_TS_d18OT1 SMALLDATETIME NULL,
  fld_TS_d19OT1 SMALLDATETIME NULL,
  fld_TS_d20OT1 SMALLDATETIME NULL,
  fld_TS_d21OT1 SMALLDATETIME NULL,
  fld_TS_d15tt SMALLDATETIME NULL,
  fld_TS_d16tt SMALLDATETIME NULL,
  fld_TS_d17tt SMALLDATETIME NULL,
  fld_TS_d18tt SMALLDATETIME NULL,
  fld_TS_d19tt SMALLDATETIME NULL,
  fld_TS_d20tt SMALLDATETIME NULL,
  fld_TS_d21tt SMALLDATETIME NULL,
  fld_TS_wk3comm VARCHAR(50) NULL,
  fld_TS_wk3total DECIMAL(18, 6) NULL,
  fld_TS_d22s SMALLDATETIME NULL,
  fld_TS_d23s SMALLDATETIME NULL,
  fld_TS_d24s SMALLDATETIME NULL,
  fld_TS_d25s SMALLDATETIME NULL,
  fld_TS_d26s SMALLDATETIME NULL,
  fld_TS_d27s SMALLDATETIME NULL,
  fld_TS_d28s SMALLDATETIME NULL,
  fld_TS_d22e SMALLDATETIME NULL,
  fld_TS_d23e SMALLDATETIME NULL,
  fld_TS_d24e SMALLDATETIME NULL,
  fld_TS_d25e SMALLDATETIME NULL,
  fld_TS_d26e SMALLDATETIME NULL,
  fld_TS_d27e SMALLDATETIME NULL,
  fld_TS_d28e SMALLDATETIME NULL,
  fld_TS_d22b SMALLDATETIME NULL,
  fld_TS_d23b SMALLDATETIME NULL,
  fld_TS_d24b SMALLDATETIME NULL,
  fld_TS_d25b SMALLDATETIME NULL,
  fld_TS_d26b SMALLDATETIME NULL,
  fld_TS_d27b SMALLDATETIME NULL,
  fld_TS_d28b SMALLDATETIME NULL,
  fld_TS_d22t SMALLDATETIME NULL,
  fld_TS_d23t SMALLDATETIME NULL,
  fld_TS_d24t SMALLDATETIME NULL,
  fld_TS_d25t SMALLDATETIME NULL,
  fld_TS_d26t SMALLDATETIME NULL,
  fld_TS_d27t SMALLDATETIME NULL,
  fld_TS_d28t SMALLDATETIME NULL,
  fld_TS_d22OT1 SMALLDATETIME NULL,
  fld_TS_d23OT1 SMALLDATETIME NULL,
  fld_TS_d24OT1 SMALLDATETIME NULL,
  fld_TS_d25OT1 SMALLDATETIME NULL,
  fld_TS_d26OT1 SMALLDATETIME NULL,
  fld_TS_d27OT1 SMALLDATETIME NULL,
  fld_TS_d28OT1 SMALLDATETIME NULL,
  fld_TS_d22tt SMALLDATETIME NULL,
  fld_TS_d23tt SMALLDATETIME NULL,
  fld_TS_d24tt SMALLDATETIME NULL,
  fld_TS_d25tt SMALLDATETIME NULL,
  fld_TS_d26tt SMALLDATETIME NULL,
  fld_TS_d27tt SMALLDATETIME NULL,
  fld_TS_d28tt SMALLDATETIME NULL,
  fld_TS_wk4comm VARCHAR(50) NULL,
  fld_TS_wk4total DECIMAL(18, 6) NULL,
  fld_TS_d29s SMALLDATETIME NULL,
  fld_TS_d30s SMALLDATETIME NULL,
  fld_TS_d31s SMALLDATETIME NULL,
  fld_TS_d32s SMALLDATETIME NULL,
  fld_TS_d33s SMALLDATETIME NULL,
  fld_TS_d34s SMALLDATETIME NULL,
  fld_TS_d35s SMALLDATETIME NULL,
  fld_TS_d29e SMALLDATETIME NULL,
  fld_TS_d30e SMALLDATETIME NULL,
  fld_TS_d31e SMALLDATETIME NULL,
  fld_TS_d32e SMALLDATETIME NULL,
  fld_TS_d33e SMALLDATETIME NULL,
  fld_TS_d34e SMALLDATETIME NULL,
  fld_TS_d35e SMALLDATETIME NULL,
  fld_TS_d29b SMALLDATETIME NULL,
  fld_TS_d30b SMALLDATETIME NULL,
  fld_TS_d31b SMALLDATETIME NULL,
  fld_TS_d32b SMALLDATETIME NULL,
  fld_TS_d33b SMALLDATETIME NULL,
  fld_TS_d34b SMALLDATETIME NULL,
  fld_TS_d35b SMALLDATETIME NULL,
  fld_TS_d29t SMALLDATETIME NULL,
  fld_TS_d30t SMALLDATETIME NULL,
  fld_TS_d31t SMALLDATETIME NULL,
  fld_TS_d32t SMALLDATETIME NULL,
  fld_TS_d33t SMALLDATETIME NULL,
  fld_TS_d34t SMALLDATETIME NULL,
  fld_TS_d35t SMALLDATETIME NULL,
  fld_TS_d29OT1 SMALLDATETIME NULL,
  fld_TS_d30OT1 SMALLDATETIME NULL,
  fld_TS_d31OT1 SMALLDATETIME NULL,
  fld_TS_d32OT1 SMALLDATETIME NULL,
  fld_TS_d33OT1 SMALLDATETIME NULL,
  fld_TS_d34OT1 SMALLDATETIME NULL,
  fld_TS_d35OT1 SMALLDATETIME NULL,
  fld_TS_d29tt SMALLDATETIME NULL,
  fld_TS_d30tt SMALLDATETIME NULL,
  fld_TS_d31tt SMALLDATETIME NULL,
  fld_TS_d32tt SMALLDATETIME NULL,
  fld_TS_d33tt SMALLDATETIME NULL,
  fld_TS_d34tt SMALLDATETIME NULL,
  fld_TS_d35tt SMALLDATETIME NULL,
  fld_TS_wk5comm VARCHAR(50) NULL,
  fld_TS_wk5total DECIMAL(18, 6) NULL,
  fld_TS_monthlytotal DECIMAL(18, 6) NULL,
  fld_TS_d1sd VARCHAR(15) NULL,
  fld_TS_d2sd VARCHAR(15) NULL,
  fld_TS_d3sd VARCHAR(15) NULL,
  fld_TS_d4sd VARCHAR(15) NULL,
  fld_TS_d5sd VARCHAR(15) NULL,
  fld_TS_d6sd VARCHAR(15) NULL,
  fld_TS_d7sd VARCHAR(15) NULL,
  fld_TS_d8sd VARCHAR(15) NULL,
  fld_TS_d9sd VARCHAR(15) NULL,
  fld_TS_d10sd VARCHAR(15) NULL,
  fld_TS_d11sd VARCHAR(15) NULL,
  fld_TS_d12sd VARCHAR(15) NULL,
  fld_TS_d13sd VARCHAR(15) NULL,
  fld_TS_d14sd VARCHAR(15) NULL,
  fld_TS_d15sd VARCHAR(15) NULL,
  fld_TS_d16sd VARCHAR(15) NULL,
  fld_TS_d17sd VARCHAR(15) NULL,
  fld_TS_d18sd VARCHAR(15) NULL,
  fld_TS_d19sd VARCHAR(15) NULL,
  fld_TS_d20sd VARCHAR(15) NULL,
  fld_TS_d21sd VARCHAR(15) NULL,
  fld_TS_d22sd VARCHAR(15) NULL,
  fld_TS_d23sd VARCHAR(15) NULL,
  fld_TS_d24sd VARCHAR(15) NULL,
  fld_TS_d25sd VARCHAR(15) NULL,
  fld_TS_d26sd VARCHAR(15) NULL,
  fld_TS_d27sd VARCHAR(15) NULL,
  fld_TS_d28sd VARCHAR(15) NULL,
  fld_TS_d29sd VARCHAR(15) NULL,
  fld_TS_d30sd VARCHAR(15) NULL,
  fld_TS_d31sd VARCHAR(15) NULL,
  fld_TS_d32sd VARCHAR(15) NULL,
  fld_TS_d33sd VARCHAR(15) NULL,
  fld_TS_d34sd VARCHAR(15) NULL,
  fld_TS_d35sd VARCHAR(15) NULL 
)

As you scrolled past the 264 columns, you may have noticed "fld_TS_d35sd" - a column name indicating that each row stores 35 days. There was a "good" reason for this, as it turned out.

Timesheets were displayed one week at a time, with the week beginning on Monday. Unfortunately, not all months begin on Monday, so the originally developer decided to use 35 days. If a month began on a Tuesday, the first day of the month was recorded in the fld_TS_d2* columns.

"There were several problems with this set up," Erik noted, "but I'll leave those as an exercise for the reader to imagine."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!