Poor Database Design

For the past month, I’ve been working part time on a Microprocessor selection tool (micro-tool) that my company plans to use to reduce the time it takes to run a trade study for selecting microprocessors for new projects. The primary aim of the program is to encourage/ force the technical leads to re-use processors from old projects to reduce the learning curve of new parts. This will facilitate code re-use, will allow re-use of existing compilers, and will decrease the time to a final schematic layout. (Surprise, parts often don’t work exactly like the spec. sheets state, and prototype boards must be built to discover differences between real parts and the spec sheet.)

I was the third developer to work on the code, the first being an intern who left at the end of last summer; the second was a junior developer who was let go during layoffs in November. Needless to say, the code was not in great shape. I spent a day looking over the code, held a meeting with the project sponsor, wrote up a short requirement description and got to work.

After two weeks time, the program was running. Searches returned the correct data, the user could enter their trade study criteria, rank the various results against each criteria, and everything was exported to an excel spreadsheet so the artifacts could be archived for future reference. The project sponsor was thrilled, “Now, let’s get this running with the production database tables…”

“Tables?” I asked. Turns out, contrary to what everyone had said previously, the micro-tool would be searching not against one table, but five. Furthermore, the five tables were not homogeneous.

1. Same type of data was encoded with different data types in different tables. Integer in two, floating point in two, and a string in the last.
2. Values were stored with different scales. RAM size was stored as bytes in four tables and as words in the fifth.
3. Key search values, such as ROM size, did not exist in all five tables.

Liberal usage of projecting new columns, renaming columns, and unions allowed me to extend my search routine to all five tables within a day. In a similar amount of time, I extended the display portion of the program to notify users from with database table a result originated.

I recommended merging the various tables, or at least fixing the data types to make them the same across all tables and was given a big red light. Supposedly, there are applications that the electrical engineers use that are tied to the current schema, and they don’t want to risk them breaking. Even after I explained how database views could be used to prevent changes in existing applications. While taking advantage of adding data consistency to the database. It would help if the database administrator was a trained in database management, instead of electrical engineering.