Human Relationships and Address

[patriciavf]

Hello All,

I am using Allen Browne's example database sample "Human"
http://allenbrowne.com/AppHuman.html
as a starting point for a database that I am redesigning. As in his
example the design is based on a definition of a Client as an
Organization, Household, or Individual with all of these stored in a
single client table. These members of an organization or household
are then associated to each other through groups.

Allen if you are reading, I hope I have not done a disservice to your
sample with my brief description.

My questions is how to manage the many to many relationship between
Addresses and Clients at the form level since the flexible table
structure will allow an association from any type of
client(organization, household, contact) to an address.

Table Structure:
Client
ClientID

Address
AddressID

ClientAddress
ClientID
AddressID

Contacts in a household will only have one shared home address.

Contacts in organizations however may have the same address as the
Organization; however, I need to account for different floor numbers,
suite numbers, mailstops, or perhaps even different street numbers. I
will also have the same issue with phone/fax numbers.

I am struggling with which form entry should take place on and how to
associate individual contacts to an address if it is different from
the organization in which they are associated to. Naturally I would
like to eliminate duplicate entry of the same address at the contact
level. I would like to be able to auto-populate the main
organizational address and allow the user to edit it create a new
address and associate it to the contact where need.

Any assistance in getting pointed in the right direction is greatly
appreciated.


BTW- In some cases mailing may be to a specific individual in a
household; however this would be handled by mailing lists and not by
address entries.

 

[Allen Browne]

Hi Patricia. I'm not sure there is a one-size-fits-all answer to the
question you ask about how to connect clients and addresses. But if it's any
encouragement, you are asking the right questions, i.e. you the way you are
going about this will help you define what is best for your situation.

After defining what "client" means you you (person, household, company,
organization - anyone or anything you deal with/sell to, possibly including
those with special roles such as your staff, suppliers, shippers, ...), the
next question becomes how to connect clients and addresses. Three basic
possibilities arise:

a) You want a fully relational database, where one client can have many
addresses (street, postal, ...) and one address can be for many clients
(members of the household, employees of the firm, etc.)

b) You are totally focused on clients, and you just want to record one or
more contact addresses independently for each client.

c) You are totally focused on addresses, and you just want to record the
names of the people at an address.

(a) implies a many-to-many relation between clients and addresses.
You therefore have 3 tables:
- tblClient: one record for each "client", with a ClientID primary key
- tblAddress: one record for each address, with an AddressID p.k.
- tblClientAddress: a record for each address of each client, with fields
such as:
o ClientID who this is for
o AddressID what address applies to this client
o Priority Number lower numbers indicate preferred
address.
o StartDate Date/Time when the address is operational from
o EndDate Date/Time when the address no longer applies (null if
current.)
This is the most powerful approach, but takes more work to interface.

(b) just treats addresses as applicable to a client. It simplifies the
design (omitting the 3rd table), treating the client/address relationship as
a one-to-many instead of a many-to-many. It is not suitable if you want to
ask about who a person lives with, but for some scenarios you are not really
interested in that.

(c) is the flip side of (b), i.e. treating address/client as a one-to-many.
Generally not as useful.

After choosing your basic strategy, other possibilities arise, like the
notion of inherited addresses. If you database indicates that client 29 (a
person) is an employee of client 57 (a company), then it is possible to
consider the addresses of client 57 as applicable to client 29 also in the
context in which 29 is related to 57. That is if 29 works for 57, then 57's
addresses may be suitable "work" addresses for 29. Similarly, if client 31
(person) is a member of client 63 (household), then client's 63 addresses
may be suitable "home" addresses for 31.

This gives you astounding flexibility to record addresses directly for the
client, and also to deduce addresses applicable to the person in different
scenarios. Say Jo works Mondays and Tuesday for Shell Oil, and Wednesday and
Thursday for Bethesda Hospital. On some occasions you deal with her in one
context, and in some the other. Since her "work" address is derived from the
parent client, you can determine the appropriate work address to use for the
context in which you are dealing with her on a particular occasion.

(BTW, that's just a function of nesting clients, so it remains true
regardless of whether you chose (a), (b), or (c) above.)

Now you have a (surprisingly simple) data structure that handles all the
oddities of real life, but the practical problem is the data entry operator.
The structure is wonderfully flexible, but does the secretary who enters the
data know where an address should go? If a person can have their own
addresses, and also inherit addresses from the groups they belong to, does
our secretary know where to enter the address? This is going to need some
careful though, good interface design, documentation, and some training to
the end user understands the flexibility they have been given, and how to
use that power.

I haven't tried to tell you what you should do, but hopefully that's enough
to trigger some good, creative thinking about the best way forward for your
particular needs.

 

[patriciavf]

Hi Patricia. I'm not sure there is a one-size-fits-all answer to the
question you ask about how to connect clients and addresses. But if it's any
encouragement, you are asking the right questions, i.e. you the way you are
going about this will help you define what is best for your situation.

After defining what "client" means you you (person, household, company,
organization - anyone or anything you deal with/sell to, possibly including
those with special roles such as your staff, suppliers, shippers, ...), the
next question becomes how to connect clients and addresses. Three basic
possibilities arise:

a) You want a fully relational database, where one client can have many
addresses (street, postal, ...) and one address can be for many clients
(members of the household, employees of the firm, etc.)

b) You are totally focused on clients, and you just want to record one or
more contact addresses independently for each client.

c) You are totally focused on addresses, and you just want to record the
names of the people at an address.

(a) implies a many-to-many relation between clients and addresses.
You therefore have 3 tables:
- tblClient: one record for each "client", with a ClientID primary key
- tblAddress: one record for each address, with an AddressID p.k.
- tblClientAddress: a record for each address of each client, with fields
such as:
    o ClientID        who this is for
    o AddressID    what address applies to this client
    o Priority         Number        lower numbers indicate preferred
address.
    o StartDate     Date/Time     when the address is operational from
    o EndDate      Date/Time     when the address no longer applies (null if
current.)
This is the most powerful approach, but takes more work to interface.

(b) just treats addresses as applicable to a client. It simplifies the
design (omitting the 3rd table), treating the client/address relationship as
a one-to-many instead of a many-to-many. It is not suitable if you want to
ask about who a person lives with, but for some scenarios you are not really
interested in that.

(c) is the flip side of (b), i.e. treating address/client as a one-to-many..
Generally not as useful.

After choosing your basic strategy, other possibilities arise, like the
notion of inherited addresses. If you database indicates that client 29 (a
person) is an employee of client 57 (a company), then it is possible to
consider the addresses of client 57 as applicable to client 29 also in the
context in which 29 is related to 57. That is if 29 works for 57, then 57's
addresses may be suitable "work" addresses for 29. Similarly, if client 31
(person) is a member of client 63 (household), then client's 63 addresses
may be suitable "home" addresses for 31.

This gives you astounding flexibility to record addresses directly for the
client, and also to deduce addresses applicable to the person in different
scenarios. Say Jo works Mondays and Tuesday for Shell Oil, and Wednesday and
Thursday for Bethesda Hospital. On some occasions you deal with her in one
context, and in some the other. Since her "work" address is derived from the
parent client, you can determine the appropriate work address to use for the
context in which you are dealing with her on a particular occasion.

(BTW, that's just a function of nesting clients, so it remains true
regardless of whether you chose (a), (b), or (c) above.)

Now you have a (surprisingly simple) data structure that handles all the
oddities of real life, but the practical problem is the data entry operator.
The structure is wonderfully flexible, but does the secretary who enters the
data know where an address should go? If a person can have their own
addresses, and also inherit addresses from the groups they belong to, does
our secretary know where to enter the address? This is going to need some
careful though, good interface design, documentation, and some training to
the end user understands the flexibility they have been given, and how to
use that power.

I haven't tried to tell you what you should do, but hopefully that's enough
to trigger some good, creative thinking about the best way forward for your
particular needs.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.















- Show quoted text -

Hi Allen,
Thank you so much for the response and the words of encourage. I will
digest and post back with any questions.