Bill, In my main membership database I use the following structure:
RegisterofMbrs - master table with Name(s), and other key information. Primary key is MbrNumber Trigger Maintained count of records in slave tables Audit information on changes to slave tables MbrCurHomeInfo - slave table. Current Address, Phone Numbers. Source codes. MbrNumber Foreign Key ref to RegisterofMbrs MbrPrvHomeInfo - slave table. Previous Address history. MbrNumber Foreign Key ref to RegisterofMbrs. Updated by Trigger MbrCurWorkInfo - slave table. same as above but for work address items MbrPrvWorkInfo - slave table same ad above but for work history items MbrCurEMailInfo - slave table. current email address MbrPrvEMailInfo - slave table previous email address history Use of VIEWS employing nested OUTER JOINS allow you to maintain variable number of records in slave tables. My system makes extensive us of BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE triggers to maintain recored counts, Audit action, date, time codes, VERFICATIONS REOUTINES. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: Albert Berry <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Tuesday, February 21, 2012 10:51 PM >Subject: [RBASE-L] - Re: Too relational? > >Yep, I would have a Persons master table, a PersonStatus table and a >PersonAddress table containing address(es) with a switch to indicate Active or >Inactive status, and follow that along. If a person lives in Hawaii for the >winter and in British Columbia in the summer, both addresses would be in the >PersonAddresses table with one only allowed to be active at any given time. >Saves a lot of keying. You could go as far as PersonPhones if you really need >to, adding a phonetype column to indicate cell, land, satellite, fax ... > >Albert > >On 21/02/2012 6:04 PM, William Stacy wrote: >> In my efforts to "normalize" my database, I'm even finding the need to split >> off postal addresses, telephone numbers, etc into separate tables. >> Presently all addresses and phones etc reside in a person table and/or in a >> family table. I haven't done this yet, but am thinking about it. In the >> end, my "family" table may end up only having about 3 or 4 columns, which >> identify the person's mom and dad for blood relative connections, and maybe >> a responsible person (bill-to and family addressee) connections. Can't >> think of much else that is needed there. >> >> My reason is this: many people have alternate addresses, some more than 2 >> such as office addresses, PO Boxes, military addresses, vacation homes, >> bill-to addresses, girl-friend or sugar-daddy addresses and so on. Same >> thing is true of phone numbers. Really, these are almost one-to-many >> items. In the reverse fashion from what you might think. Really, a single >> street address can have many people associated with it, and the address >> itself really doesn't change, only the residents do. Same thing is true of >> phone numbers. My cell num is unique, but after I give it up, someone else >> will eventually get it. >> >> Anyone construct tables thusly? >> >> -- William Stacy, O.D. >> >> Please visit my website by clicking on : >> >> http://www.folsomeye.net >> >> >> > > > > >
