I appreciate this article and discussion.

I have to do ad-hoc report queries off of a Sybase database created by an other agency (let's call them the State). I have zero input on database design, practically no documentation, and only have read access to the data tables.

I cringe every time my users ask for queries that require finding what "program" a client is in. Why? Because the State created a set of 8 columns all containing "program" data. Users can enter 3 characters into each column to indicate the set of programs the client has. A client can have anywhere from zero up to 8 programs. There is no order to the columns and more than 8 possible programs, though the client will never have more than 8 programs at once.

What this leaves me with is this: When the users ask, "How many clients have program ABC?" Then I have to search all 8 columns. And of course, it is never as easy as that. They usually have queries like, "How many clients have any of the following programs, ABC, XFG, T3B, ... but not GTH or XYZ."

So, why did the State set up the Sybase database that way? Don't they know anything about database design? I'm not privy to any of their conversations or programming problems. So, I don't know. But most of their database (while not a schema I would choose) is fairly workable and mostly normalized (though they did do that annoying phone thing talked about in the article). Why then set up denormalized data for something as vital as a client's program?

My thoughts:

1) The program data is part of a set of data that has to mirror/be merged with a legacy mainframe database. I have no doubt that the mainframe system is set up with the same 8 columns.

2) But that doesn't explain why they wouldn't use programming to normalize the data in the database I access. All I can think of here is that they weren't thinking about querying the data and they were mostly concerned with saving time on programming the part that merges data between the mainframe and the normal database.

I'll tell you, if it was me, I would have done the extra programming so that the data was normalized in the Sybase database. It is a terrible pain working with their data as-is!

But as the article points out, when you don't have a choice, it is nice to have techniques to handle the situation. I just thought people might appreciate another real world example of this kind of problem. Cheers.