Hi,
I was just wondering why you don't use 1 Master + 1 slave and in the slave add
a field: Active Yes/No to show/use the Current Record ?
Marco
----- Original Message -----
From: James Bentley
To: RBASE-L Mailing List
Sent: Wednesday, February 22, 2012 7:56 AM
Subject: [RBASE-L] - Re: Too relational?
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
>
>
>
