modelling qualifiers

View previous topic View next topic Go down

modelling qualifiers

Post  johan on Tue Mar 27, 2012 5:08 am

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

johan

Posts : 5
Join date : 2012-02-09

View user profile

Back to top Go down

Re: modelling qualifiers

Post  ngalemmo on Tue Mar 27, 2012 7:34 am

Rotate the data so you have one qualifier per row.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: modelling qualifiers

Post  johan on Tue Mar 27, 2012 8:15 am

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

johan

Posts : 5
Join date : 2012-02-09

View user profile

Back to top Go down

Re: modelling qualifiers

Post  ngalemmo on Tue Mar 27, 2012 8:38 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: modelling qualifiers

Post  johan on Tue Mar 27, 2012 8:45 am

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

johan

Posts : 5
Join date : 2012-02-09

View user profile

Back to top Go down

Re: modelling qualifiers

Post  ngalemmo on Tue Mar 27, 2012 9:25 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: modelling qualifiers

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum