Thursday, October 07, 2010

Data modeling is not for humans

kw: computer science, data modeling

I'm taking an afternoon break from the onerous task of writing specifications for a new database and its workflow application. I haven't done data modeling for a few years, and the rust is evident! This kind of work makes more demands on the memory and imagination than anything else I can think of.

I hate to get halfway through a spec document and run into this kind of conundrum: We have two similar items, sets of vocabulary terms. One set is preferred, the other deprecated. One set must be unique, but the other, which embodies synonyms and abbreviations to the first list, need not be. In other words, particularly for the abbreviations, a deprecated term can have more than one interpretation from the preferred list. Wasn't that as clear as mud?!

OK, our database programmer prefers to put both lists into a master vocabulary table with a flag field that indicates which type each term is. The conceptual database looked fine, and even the logical data model seems workable, but going to the physical model, things get creaky. I am not sure a uniqueness constraint can be enforced for one type of term but not the other. I think I'll have to go back and talk everyone into having different data tables for these, which means rewriting about a third of what I've already done. Oh, joy!

Not the first time I've had to do a massive rewrite. Just a pain. Back to the grindstone…

No comments: