Bill, There is a point where you can become too relational. Sure I could break down the address into its component parts number, pre-directional, street name, street type, post-directional etc.
The problem becomes in putting it all back together. In a majority of the cases the overhead of breaking it down, linking to appropriate support tables, maintaining the parts, reassembling for presentation purposes way out way the benefits of decomposing it in the first place. Also, what works for U.S. and Canadian addresses generally do not work for international addresses. In addition, Business addresses have a different structure than residential addresses or Post Office Box addresses. Best to keep it simple. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: William Stacy <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Thursday, February 23, 2012 9:22 AM >Subject: [RBASE-L] - RE: Too relational? > > >I think people can not only have several postal addresses, but they can have >several telephone #s, multiple work lines, faxes, etc. even multiple e-mails >are popular. But you're right about the shape thing. I'm thinking that the >street name and the city,state zip line probably belong in their own tables as >lookups. I think Folsom CA 95630 should only exist once in my entire >database, not the thousands of times it now does. But that brings me back to >my title question of this thread... > > >On Thu, Feb 23, 2012 at 4:38 AM, Bill Downall <[email protected]> >wrote: > >William, >> >> >>This is a design dilemma. Addresses are definitely a separate table, because >>a person can have multiple addresses, and because the "shape" of the data is >>different, (street address, city, state, postal code, country). But do the >>phones and emails and twitter accounts link back to the people, or to the >>addresses? I tend to go with linking to the people, with phone types broken >>down in "home phone" "mobile phone" "work phone", etc. >> >>Bill >> >> >>On Thu, Feb 23, 2012 at 7:25 AM, William Stacy <[email protected]> >>wrote: >> >>Now this is interesting. Do you include Postal contact types the same way, >>in the same table? Are these 2 columns part of a personal demographic table, >>or a separate table. If the latter, how do you link them up with the >>personal table? TIA >>>> >>> >>>On Wed, Feb 22, 2012 at 5:59 PM, Bill Downall >>><[email protected]> wrote: >>> >>>I cannot see the future as well as you, Mike. But my more recent designs do >>>not have any columns with the letters p-h-o-n-e in a column name. There is >>>a column for ContactType, and another for ContactValue. I could someday add >>>a new contact type of ipv6, in addition to existing types of email, mobile, >>>work, google voice, twitterID, etc. No schema change needed. >>>>Bill >>>>On Feb 22, 2012 5:46 PM, "Mike Byerley" <[email protected]> wrote: >>>> >>>>I started using nnn.nnn.nnnn for phone numbers anticipating at some time sub >>>>>ipv6, phones will just be IP numbers. Just a guess though. >>>>> >>>>> >>>>>----- Original Message ----- >>>>>From: "Bill Downall" <[email protected]> >>>>>To: "RBASE-L Mailing List" <[email protected]> >>>>>Sent: Wednesday, February 22, 2012 11:26 AM >>>>>Subject: [RBASE-L] - RE: Too relational? >>>>> >>>>> >>>>>It's nice to see Professor Wills here! You know a topic like this would get >>>>>him going. >>>>> >>>>>Bill, in my mind, a basic reason to normalize fully is to create a database >>>>>that is least likely to need either schema changes or awkward >>>>>exception-handling down the road. >>>>> >>>>>If you do not normalize, and you provide room for 3 phone numbers, some day >>>>>you will have to put the fourth phone number in the comments, or change the >>>>>schema to allow for 4 phone numbers. >>>>> >>>>>Schema changes are expensive, because all forms and reports and procedures >>>>>and eeps and views and rules and triggers and applications that relate to >>>>>that data may have to be changed, too, and cannot be done by users through >>>>>"settings", but have to be done by programmers. >>>>> >>>>>Putting the data in the "wrong" place like the comments means people won't >>>>>find that data with a normal search or query. >>>>> >>>>>There are other good reasons to normalize, like not "wasting" columns that >>>>>are usually blank, and not having to search three or five columns instead >>>>>of one (For example, to determine what customer might have sent us an >>>>>incomplete or garbled fax message or credit card transaction where all we >>>>>know is that their address is "345 Main Street"). But avoiding future >>>>>expensive schema changes is the main one. >>>>> >>>>>Bill >>>>> >>>>> >>>>>On Wed, Feb 22, 2012 at 11:02 AM, Wills, Steve <[email protected]> wrote: >>>>> >>>>>> “Too relational” is a state that is rarely achieved, IMHO. I think your >>>>>> issue/question often and I like the direction of your thinking. I guess >>>>>> that thinking about such makes me a little “twisted” to some. I also own >>>>>> my own barcode-scanner - well enough about my predilections!**** >>>>>> >>>>>> >>>>> >>>>> >>>>> >>> >>> >>> >>>-- >>>William Stacy, O.D. >>> >>>Please visit my website by clicking on : >>> >>>http://www.folsomeye.net >>> >>> >>> >>> >> > > >-- >William Stacy, O.D. > >Please visit my website by clicking on : > >http://www.folsomeye.net > > > > > >
