[AccessD] Database Design

...I'd use a separate street address table with yes/nos for mailing and 
billing ...this ideally allows sharing of common addresses such as family or 
business...as well as a separate table for geo data including postal code, 
city, state, the geoID being an fk in the address table.

...more important than the address considerations, is the structure of your 
entityIDs because you need to be able to track familial and business 
relationships that often overlap ...I prefer using a super entity table with 
sub-entities such as companies and individuals as child tables.

...the street address table then becomes a child of the super entity as well 
...along with tables for phones, e-mail, etc

...this approach would give you a 3rd normal relational design and the 
ability to do highly accurate mailings ...although it is easier to maintain, 
it does require more join tables and more complex queries than the default 
alternative of keeping addresses in the same table with the entity data.

William Hindman

----- Original Message ----- 
From: "Barbara Ryan" <BarbaraRyan at cox.net>
To: "Access List" <AccessD at databaseadvisors.com>
Sent: Wednesday, February 28, 2007 1:30 PM
Subject: [AccessD] Database Design


>I am designing a new Access database for a therapeutic equestrian riding 
>facility.
>
> The database holds names of all the "partners' (individuals, groups, and 
> companies) who volunteer, donate, and/or ride horses.  This database will 
> primarily be used for mailing thank you notes, information about upcoming 
> events, etc.  Each "partner" will have a mailing address and (optionally) 
> a billing address.  Many partners will share the same mailing address 
> (e.g., handicapped individuals who live in a facility, or a family where 
> mom and dad are volunteers and their child is a rider).
>
> Selection of partners for mailings needs to be very flexible --- sometimes 
> a mailing is "one per household" --- however, each rider in a facility 
> should receive a separate letter.  Sometimes mailings will only go to 
> donors and volunteers; sometimes just to riders, etc, etc.
>
> Would you split addresses into a separate table or include mailing/billing 
> address information in the Partners table (and provide a mechanism to 
> "copy" an address from an existing in the same household)?
>
> Thanks,
> Barb Ryan
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>