modelling qualifiers

Hi guys,

I am confronted with a difficult problem.
One table in the source system contains 3 qualifier columns with 3 qualifiervalue columns, for example:
Qualifier1 Qualifier1Value Qualifier2 Qualifier2Value Qualifier3 Qualifier3Value
Excess kilometer cost 5 Less kilometer cost 3 ReliefVehicleTime Within 3 hours

For each client the Qualifier columns can contain different Qualifier types and values.
The requirement is to have access to those qualifiers in an easy way.
Like counting all customers that have a ReliefVehicleTime within 3 hours, but this qualifier can appear in all 3 qualifier columns.
Is it a good idea to put all qualifiertypes in one column and all qualifiervalues in one column (union) and then 'pivot' the types into column headers for access by the users? Or do you propose another solution?

Thanks for your help!

Johan

Rotate the data so you have one qualifier per row.

That will multiply the number of records by 3 as each record has 3 qualifier columns.

Yes. And... how else would you do it? As you said, a qualifier can appear in any of the three columns. Such a structure is very difficult to query. You can't go to a one column per qualifier structure as loading it would require complex processing, the row could be very wide, plus if they invent a new qualifier you need to alter code and data structures.

We are also looking at a one column per qualifier structure as it offers the best usability for a user. We understand that this requires some maintenance, but we don't think it will change very often.

Johan

Are the users hand-writing SQL queries, or do you have a BI tool? Don't confuse storage with presentation.

How many qualifiers are there? If there are only 5 or 6 and it has been that way for as long as anyone can remember, then fine. If you have dozens, then it is not such a good idea. It is easy enough to rotate the data back to a flat form in a BI tool, or by defining a view. If they add new codes in the future, it is just a matter of changing the BI tool metadata or the view. It doesn't require a full-on development project to fix the load.