October 26, 2011 at 10:44 am
I have a warehouse where there is a product code field, and this is 99% of the time queried for a single product code, which matches to a single client, which works just fine.
We have a single client which is an exception, this client has a handful of product codes, which alway need to be pulled for any process which has to do with the client. In the past this handful of codes has been static, for many years, and we simply all knew which codes to pull for this particular client.
Recently this list has altered, meaning going back through code and looking for the places the product codes are used. Clearly this is not a good way to do this.
Are there any typical strategies or simpler to maintain methods to handle a 1% exception situation? It seems to my mind, a bit overkill to create a separate lookup table for this one client.
October 26, 2011 at 10:57 am
David Lester (10/26/2011)
I have a warehouse where there is a product code field, and this is 99% of the time queried for a single product code, which matches to a single client, which works just fine.We have a single client which is an exception, this client has a handful of product codes, which alway need to be pulled for any process which has to do with the client. In the past this handful of codes has been static, for many years, and we simply all knew which codes to pull for this particular client.
Recently this list has altered, meaning going back through code and looking for the places the product codes are used. Clearly this is not a good way to do this.
Are there any typical strategies or simpler to maintain methods to handle a 1% exception situation? It seems to my mind, a bit overkill to create a separate lookup table for this one client.
If I understand correctly, you have a many-to-many relationship between product and client...for this one exception client only.
A simple bridge-table will resolve the issue. I don't think it's overkill to create such a bridge table, because the benefits (standardizing code; no more hard-coding; no development time should the products change) far outway the cost of storage and implementation.
Requirements change over time and there is no guarantee that other clients won't become "exceptions" in future too.
Another possible option may be to alter your product dimension, to contain the key of the client. The client key will also then become part of the business/natural key. Doing it this way will mean that you effectively will have multiple entries for the one client who is the exception. I am of course assuming here that each product currently only relates to one client...forget I said this though if I miss-interpreted your post.
Martin.
October 26, 2011 at 11:07 am
You do have the right idea. One to One for all but a single client, whom is one to many. While there is no guarantee this will be the only exception, it has been the only exception in twenty years.
I will almost certainly end up with a dimension table, though I am curious what other ways are viable to do such a thing. By viable I mean something that does not kill performance, nor make a DBA shudder, heh.
October 26, 2011 at 11:12 am
David Lester (10/26/2011)
You do have the right idea. One to One for all but a single client, whom is one to many. While there is no guarantee this will be the only exception, it has been the only exception in twenty years.I will almost certainly end up with a dimension table, though I am curious what other ways are viable to do such a thing. By viable I mean something that does not kill performance, nor make a DBA shudder, heh.
heheh...understood.
Bridge-tables are quite common when you have a many-to-many relationship between dimensions, and performance will be good if you index correctly...the table will be very narrow after all. Be careful though if you have type-2 dimensions...that will change the situation considerably.
One other thing to note is that in a perfect world (and star-schema), most references to your dimensions will be through a fact table which means that the bridge-table will not come into play at all.
Hope this helps.
Martin.
October 27, 2011 at 7:00 am
David Lester (10/26/2011)
I have a warehouse where there is a product code field, and this is 99% of the time queried for a single product code, which matches to a single client, which works just fine.We have a single client which is an exception, this client has a handful of product codes, which alway need to be pulled for any process which has to do with the client. In the past this handful of codes has been static, for many years, and we simply all knew which codes to pull for this particular client.
Recently this list has altered, meaning going back through code and looking for the places the product codes are used. Clearly this is not a good way to do this.
Are there any typical strategies or simpler to maintain methods to handle a 1% exception situation? It seems to my mind, a bit overkill to create a separate lookup table for this one client.
From the Data Modeling point of view I would say that you have a many-to-many relationship at the E/R Model - currently you have exceptions to that n-n rule that account for 99% of the cases but this ratio may change in the future.
When you move from E/R Model to Physical Model that n-n relationship has to be broken - I agree with Martin a bridge table appears to be the sensible solution.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 27, 2011 at 8:14 am
Thank you both! It is good to have confirmation that my gut reaction was on the right track. Not too bad for the accidental data warehouse designer/DBA/Developer where there are no DB people of any sort working!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply