Andrew Sullivan wrote: <blockquote cite="mid20070617155441.GB13671@phlogiston.dyndns.org" type="cite"><pre wrap="">On
Sun,Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote: </pre><blockquote type="cite"><pre wrap="">functions and
usingsubqueries without success. I think I need someone
to point me in the right conceptual direction. </pre></blockquote><pre wrap="">
Well, the right SQL-esque conceptual direction is not to have
different tables at all. That's not a very normal-form thing to do,
because the data has been broken into pieces dependent on the data
itself, rather than the kind of data it is.
A </pre></blockquote> Well, Andrew, you're certainly right but I made an exception because of the data needs. The real
applicationis a sales tax table, where the tax jurisdiction can depend on any element of the address hierarchy. In
differentareas, it could depend on zip/postal code, city, state/province, nation, or even street address. I originally
consideredstoring all address elements in one giant table with parent/child relationships (zip 11208's parent is
Brooklyn,Brooklyn's parent is Kings County, Kings County's parent is NY, etc but brief analysis showed that address
elementsfrequently have more than one parent. So I decided to keep the elements in separate tables (nation, city,
street,zip, etc) to allow more complex relationships. That led me to the sales tax jurisdiction problem. I could just
assigna tax jurisdiction to every street segment but that would create a daunting data maintenance problem as it
requirespropagating jurisdiction changes down through every segment and ensuring every valid street segment has a
record.<br/><br /> It's an interesting problem.<br /><br /> Another respondent suggested a programmed function that
willdo the job. I was hoping for a pure SQL solution but his approach will definitely work.<br /><br /> John<br />
