My personal DB hell
After reading Roy Osherove's Mortal sin on messy database's, I feel I have to share my own personal experience.
In the database for the costing request estimate system I maintain at work, there is this one table called "Document_Flows". It is essentially the heart of the system, tracking the movement of the work as goes through the various stages.
This single table is used to denote all of the follow:
- Requests for Business Assumptions
- Business Assumptions Complete
- Assignments to Application Areas
- Personnel assignments to team estimates
- Approvals up the management chain
- Production of the final, overall costing document
It tracks all of that through essentially 3 fields: AssignedByOrganization, AssignedToOrganization, and ApprovalDate/UID (always set at the same time). This table is incredibly dependent on our Organization table and the IDs of the areas. It uses them very poorly too. An AssignedByOrganization of 0 and AssignedToOrganization of 4 or 9 means Requests for Business Assumptions. When they are completed, the approval flags are set. The assignments to app groups are our org ID and the area's org ID (we assign to them). Assignment to personnel is assigned by of the team's ID and assigned to of 0. Then when assigned by and assigned to are the same, it when a team's estimate is complete. When assigned by 0 and the assigned to is another area's ID, it is approval. Then the final costing document is much the same as the approval ones.
The annoyance is none of this is documented! Anywhere! The only reason I know it is because I've maintained the system for over 2 years and had to read the code to figure it out. You also have entries of different types that look the same, which becomes problematic when we have to expand our requirements. Also, someone through it was ok to make the fields nullable! WHY! Somehow, nulls occassionally slip in and I can never figure out how or reproduce it. This table easily gets 20+ entries per estimate, and there are about 1,000 estimates in our system (that is probably conservative though, since most estimates go through a couple revisions).
I am so thankful that they're letting me completely redesign the application. Granted, I would have preferred .NET, but they're all J2EE here. It'll still be better than the mess this application is in classic ASP. Also going to redesign that table and break it up into 3-4 separate ones. Probably one for Business Assumptions, one for personnel assignments, and one for approvals (fit assignment to app areas in there too). It'll also give me a chance to fix the major pain that happens when the areas are re-organized. Relational integrity nightmare.