Forums
Joshua had recently started his job at a consulting firm. To get his feet wet, he was added to the team on an application they maintained for the juvenile court system. Fortunately, it had been developed entirely by the company, and everyone Joshua had met at the company seemed to have a good head on their shoulders. Plus it wasn't too big of an application, making it an ideal environment for Joshua to learn.
Joshua's first task was to prepare some reports for the users — an ideal first task for a developer new to a system. Except that it was practically impossible to create the reports they wanted.
The users requested a handful of fields, including the user that entered a case in the system. Which wasn't actually recorded anywhere in the system, as far as Joshua could tell. Cases had pleadings, motions, and hearings (and each had their own table), but the user that entered the case wasn't recorded as a part of any of these.
One of the developers on Joshua's team had made some efforts to record the user that performed a particular action by creating a table called "Screen." It tracked each change made on every page, and kind-of-not-really linked up with the other tables via its "contextfieldid" column, which stored caseids, motionids, pleadids, and hearingids.
The name "contextfieldid" is actually a pretty well chosen name, because you'd have to sort of guess which ID type it was pointing to based on its data. caseid was a bigint, hearingid was an nvarchar(16), motionid was an int, and pleadingid was an nvarchar(32). For simplicity and future expandability, contextfieldid was an nvarchar(255). No relationships were enforced in the database. It looked something like this...
This design made it hard to tell if contextfieldid was referencing a hearingid (nvarchar(16)) or pleadingid (nvarchar(32)) unless it was over 16 characters long, and impossible to tell if it was referring to a caseid (bigint) or motionid (int), since neither had yet reached the 2,147,483,648 mark.
This was a problem because the user ID would be required to track pay perks. Joshua mapped out a solution on his notebook and showed it to the guy who originally designed the database, Roy.
"I can see why you chose this design," said Joshua, trying to be diplomatic. "But I think we might need to tweak it a little for some of these reports the users want. I think we should make mapping tables, like case-screen, hearing-screen, and so on, with enforced relationships. What do you think?"
"That design is no good," Roy replied defensively. "How will I store all the data in one place then?"
"OK, well, I'm curious... why does the data all need to be stored in one place?"
"If we make all these tables to link everything together, what if they add to the scope? What if there are more things they want to record about a case later on? We'll keep adding tables and adding tables and before long there will be double the tables!" Roy stormed out of the office.
As the new guy, Joshua didn't have any leverage, and had to just deal with the original design. Roy wouldn't hear any of the complaints about the absence of data integrity or reliability. Joshua just has to convert every ID field into an nvarchar(255) to work with the Screen table.
[Advertisement]
BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!