May 25, 2004 at 12:23 pm
We have a SQL database that is updated via an interface to our ERP system on another platform. It is available to end users for ad hoc queries and has some common queries available as views.
I sort of inherited this thing, and my predecessor generally would use something I call a "combination" field, which is best described by an example: the key to a sales order line record is one field containing both the sales order number + the sales order line number (separated by a delimiter). Many times part of this combo-column is duplicated into the separate components, so that the sales order number can be used by itself to join with the sales order header record for example. However, currently the component fields are generally not indexed so queries can suffer in performance.
I've always been from the school where only the atomic values were stored in each column, and when necessary multiple columns would comprise the primary key. Joining to another table on multiple columns was not uncommon.
But end users here seem to like the combo-fields for ease of joining. They like the simplicity of joining two tables by drawing one link in a graphical query builder. Am I too narrow-minded to want to take this away from them to eliminate the redundancy of data and speed up their queries by having the atomic fields indexed? Should I just give in and add extra indexes to give users the best of both worlds? Are there other factors I should be considering? All opinions welcome.
May 28, 2004 at 8:00 am
This was removed by the editor as SPAM
May 28, 2004 at 11:32 am
The way I see it is....who is the database for? You or the users?
If it's you....set it up the way you want it. If it's the users....set it up their way.
BUT...if you set it up to make it easier on the users when you KNOW it might eventually be slower for them...you need to let them know that their way can cause them problems. Get it in writing so they can't complain that you aren't doing your job later on.
Remember...as much as we DBAs might hate it...the customer is right. They are the ones using it...if they don't like it 1)they won't use it or 2) they'll find someone who WILL do it their way.
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply