February 15, 2009 at 2:18 pm
That was my experience the one time I visited... I got to experience a "Southerly" and saw the temperature drop 20 degrees in under an hour.
But everything I saw was beautiful country.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 17, 2009 at 1:51 pm
You might consider de-normalizing, storing a current address in each table where it is needed, having a historical address table that could be shared by those entities (with a M:N, join table between each entity and the historical addr table). Storage is cheap, complexity is expensive... if you need to scrub and standardize addresses for amiling etc there are many type of packages you can purchase to do so eitehr on teh way in or out of your storage. Youc an also buy a compelte list of all known addresses in the USA and Canada and just join everything to that 🙂 but I have had success with denormalization-historical storage-cleaning on the way out. And lots of problems trying to get too fancy with it.
February 17, 2009 at 1:57 pm
[font="Verdana"]That can work. The issue there is around propogating address changes. If a customer changes their address for one product, should it change for all? Also, disc space is cheap, but justifying it is not, and the increase in I/O can be quite expensive. So I don't normally recommend extensive denormalisation within an OLTP database.[/font]
February 17, 2009 at 4:19 pm
Again, the point is not to delete a valid address from the primary address table just because one user quits using it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 17, 2009 at 6:21 pm
detecting duplicates is even more difficult than these other issues.
123 North Third
123 N. 3rd
123 North 3rd
123 N 3rd
123 N Third
123 North Third Ave
123 N. 3rd Ave
123 North 3rd Ave
123 N 3rd Ave
123 N Third Ave
123 North Third Ave.
123 N. 3rd Ave.
123 North 3rd Ave.
123 N 3rd Ave.
123 N Third Ave.
123 North Third Avenue
123 N. 3rd Avene
123 North 3rd Avenue
123 N 3rd Avenue
123 N Third Avenue
Could all be the same address. Or, North Third Street might be a different place than North Third Avenue. If they don't enter either St or Ave, which is it? or Road or Boulevard, or what if they leave off the "North"...
Consider the legal environmet you are in. I worked at a finance company, we had laws regulating our usage of addresses for clients, so we spent lots of moeny on standardization, cleaning tools etc. Later I worked at a insurance processor and all we cared about was did the mail get delivered or not. it is possible to drasticaly over-engineer an address maintenance system, also the opposite.
Spend the time and effort up front to investigate the legal and practical requirements and don't let your users blue-sky you into spending a thousand man-hours on an address system that will not make any difference to the bottom line. Where does the money get spent? If it takes a cust svc rep an extra 10 seconds to do a change of address in one design vs the other, how many address change transactions (at the pay rate of a CS rep) will it take to make up for the man-hours of developers and DBAs and vendor licesnes etc how many addr changes do you do a year so how many years of addr changes will it take to pay for the system? How does insuring that you have no duplicate addresses impact the company's profit margin? Lots to think about... 🙂
February 17, 2009 at 6:40 pm
detecting duplicates is even more difficult than these other issues
Yes it was, but we now have the address standardization routines in place. (You didn't mention things like unit address, P.O. direction, but those are accounted for too.) The application in question was more than the original scenario posted by the OP, and it pays for itself.
That said, I totally agree that complexity for it's own sake can make a project nonprofitable, but that's a project management issue, not an SQL issue. I often believe that trying to achieve perfection prevents us from obtaining what is optimal.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 17, 2009 at 7:31 pm
Bob Hovious (2/17/2009)
That said, I totally agree that complexity for it's own sake can make a project nonprofitable, but that's a project management issue, not an SQL issue. I often believe that trying to achieve perfection prevents us from obtaining what is optimal.
Couldn't agree more. I've been on more than one stalled project where someone wouldn't move on until it was perfect.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 5:41 am
Addresses are a pain in the tucas, and yeah, complex storage can be confusing, but I've just found that the more complex storage mechanisms provide a level of versatility on top of absolute enforcement that tends to make the data cleaner over time. I've tried the "let 'em store and we'll clean it up later" approach and "later" just never really arrives. We actually subscribe to a service that verifies addresses prior to entry to ensure it's a location that really exists,in addition to the complicated storage rules that eliminates duplicate addresses and prevents incorrect address changes. But our business model is a partly based on location (are you in a wind zone, earthquate zone, flood zone,etc.) so we kind of care more. It's not simply having the right place to send the bill (although we need that too).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply