So that thread was getting a bit threadbare, so I'm starting another just in case this one has lots of responses, which is nice to get.
I've decided to try to go more or less completely relational on communication addresses, and hope anyone who sees any errors in my ways will point them out before I spend too much time on this. My current database has about 17,000 persons (both human and companies) all in a single "pat" table that includes, among others, a wtel (work telephone) col, a mobile col, and an email col. It has about 12,000 families in a "fam" table that includes a htel (home phone) col and a fax col, as well as mailing address columns fa, fa2 and fa3. These two tables are linked through a common column "fn" or family number. Families can have from 1 to any number of persons linked to them. I have a third table that I haven't used much but which I'm pressing back into service. The "z" table contains just 5 columnns the last of which is a computed column that concatenates a city, a state and a 5 digit zip per USPS specs, more or less. I have about 185 zipcodes so formatted and plan to make heavy use of these in the new schema. I've created a new table I'm calling comad (for communications addresses) which has just 9 columns that will contain linking columns to the pat table and the z table, 2 date dton and dtoff columns for specifying active vs inactive addresses (even scheduling temporary dates on and off for vacation addresses), plus a long text address column that will hold phone #s, e-mails, web URLs, etc. and a couple of shorter ones for misc use. This table will allow me to place all the telephone, postal, web etc addresses I want all in one column (I'm thinking about 50 characters, unlimited # per person, and to dump all those confining fields I've been using in the pat and fam tables. All that duplication of city, state and zip will disappear along with the inadvertent mis-spells etc (Folsom, Foslom , Fulsom, etc.). Will work on this conversion this weekend, so if I'm missing anything, or if I'm crazy, let me know. BTW apropos to another thread, my pat table uses just 2 name fields, pf 20 char and pl 25 char which I use as follows: companies/orgs. have null pf (first name). For humans, I use pf for Mr., Dr.,Ms. and their 1st names and their middle initial or first initial, whatever) and pl for last names and things like , , M.D.; , Jr.; , Esq.; , IV. whatever they want just tacked on the end. Formats nicely and searching on the last name works like a charm. (won't work if you have to place a middle initial in some field of its own, of course, but who needs that?) -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
