modelling a contact database

[Matthew]

Maybe I should have explored this issue first.

I've designed contact databases in the past where address, telephone & email
info was stored in three different tables:
tblPeople (stores peoples' home addresses)
tblCompanies (stores companies' main addresses)
tblAffiliations (stores a person's address, tel, email where they work at
that company)

However, I've been hearing that it's better design to keep all addresses in
one table: tblAddresses. And since people aren't very different from
companies, I'm storing them in the same table: tblRecords.

So I have:

tblRecords
*RecordID
Last/CompanyName
FirstName..

tblAffiliations
*AffiliationID
ParentID (stores RecordID of company)
ChildID (stores RecordID of their employee)
UseParentAddress (Y/N)
Title
Department

tblAddresses
*AddressID
AddressTypeID
RecordID
Address
City, etc

tblEmails
*EmailID
EmailTypeID
EmailAddress

tblTypes
*TypeID
Type

tblTypes contains three records: Home/Personal, Work, Other. This way, any
address, email address or telephone number can be designated as work
address, home or other.

I like this model very much for its efficiency. The bump I'm encountering,
and maybe I'm not trying hard enough, is that it's not simple/elegant for me
to show a person's work address. If they've got UseParentAddress checked,
then how do I get the company's address to show up among that person's other
potential addresses.

I feel like I'm on the right track though, that this table design is smarter
than my previous forays. Any guidance or validation would be greatly
appreciated!!

Thanks in advance!!

Matthew

 

[Allen Browne]

Create a query that uses tblAffiliations and 2 copies of tblAddresses. Alias
the 2 copies of the address table as (say) CompanyAddress and
PersonalAddress. Join tblAffiliations.ParentID to CompanyAddress.RecordID,
and tblAffiliations.ChildID to PersonalAddress.RecordID. Change these both
to outer joins (by right-clicking on the join line, and choose option 2 or
3 - the one that reads "All records from tblAffiliations, and any matches
from ...")

You can now type an expression like this into the Field row:
IIf([UseParentAddress], CompanyAddress.Address, PersonalAddress.Address)

That's the concept anyway. Once you have that working, you can refine it to
handle what you need it to do when the company address is blank but it's
marked as the preferred address, or there are multiple addresses for a
company or person, or ...

As a general approach, it is a good idea to keep all the clients in the one
table (persons and companies), and all the addresses in the one table. What
I'm not sure about is that your tblAddresses has a RecordID foreign key. I
would have thought that one address could apply to many clients, and one
client could have multiple addresses, so you would need a tblRecordAddress
table (junction between the clients table and the addresses table.)

 

[Matthew]

Ah, that's the brilliant piece I was missing.

If there's a many-to-many relationship between tblAddresses and tblClients,
that sounds more efficient. I will explore.

Thank you so much!!

Matthew

Create a query that uses tblAffiliations and 2 copies of tblAddresses.
Alias the 2 copies of the address table as (say) CompanyAddress and
PersonalAddress. Join tblAffiliations.ParentID to CompanyAddress.RecordID,
and tblAffiliations.ChildID to PersonalAddress.RecordID. Change these both
to outer joins (by right-clicking on the join line, and choose option 2 or
3 - the one that reads "All records from tblAffiliations, and any matches
from ...")

You can now type an expression like this into the Field row:
IIf([UseParentAddress], CompanyAddress.Address,
PersonalAddress.Address)

That's the concept anyway. Once you have that working, you can refine it
to handle what you need it to do when the company address is blank but
it's marked as the preferred address, or there are multiple addresses for
a company or person, or ...

As a general approach, it is a good idea to keep all the clients in the
one table (persons and companies), and all the addresses in the one table.
What I'm not sure about is that your tblAddresses has a RecordID foreign
key. I would have thought that one address could apply to many clients,
and one client could have multiple addresses, so you would need a
tblRecordAddress table (junction between the clients table and the
addresses table.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Maybe I should have explored this issue first.

I've designed contact databases in the past where address, telephone &
email info was stored in three different tables:
tblPeople (stores peoples' home addresses)
tblCompanies (stores companies' main addresses)
tblAffiliations (stores a person's address, tel, email where they work at
that company)

However, I've been hearing that it's better design to keep all addresses
in one table: tblAddresses. And since people aren't very different from
companies, I'm storing them in the same table: tblRecords.

So I have:

tblRecords
*RecordID
Last/CompanyName
FirstName..

tblAffiliations
*AffiliationID
ParentID (stores RecordID of company)
ChildID (stores RecordID of their employee)
UseParentAddress (Y/N)
Title
Department

tblAddresses
*AddressID
AddressTypeID
RecordID
Address
City, etc

tblEmails
*EmailID
EmailTypeID
EmailAddress

tblTypes
*TypeID
Type

tblTypes contains three records: Home/Personal, Work, Other. This way,
any address, email address or telephone number can be designated as work
address, home or other.

I like this model very much for its efficiency. The bump I'm
encountering, and maybe I'm not trying hard enough, is that it's not
simple/elegant for me to show a person's work address. If they've got
UseParentAddress checked, then how do I get the company's address to show
up among that person's other potential addresses.

I feel like I'm on the right track though, that this table design is
smarter than my previous forays. Any guidance or validation would be
greatly appreciated!!

Thanks in advance!!

Matthew