modelling a contact database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


.

Relevant Pages

  • Re: ONS - loyal or not?
    ... Our needlecraft shops surely in most cases don't have what I need, ... We have wonderful needlecraft stores online. ... more of our shopping online. ... Design page http://www.KarenMCampbell.com/designs.html ...
    (rec.crafts.textiles.needlework)
  • Re: error message that says too many fields
    ... Another that stores information about Companies. ... A flat design very often leads to duplicate data in your tables. ... For most payments, only a few of those fields ... If the receipt only pays rent, you'll have a record in each table. ...
    (microsoft.public.access.gettingstarted)
  • Re: compare
    ... of these stores, this is really not a good design. ... (Pkey partNumber, Store) ... > is there anyway I could create a colmn called lowest pick the lowest price ...
    (microsoft.public.sqlserver.programming)
  • Re: Halo 3 UK street date broken (seriously)
    ... select few stores (including Shipley and Leeds Crown Point Retail Park if ... you're anywhere near me) are showing as in stock and ready to collect now. ... Reserve online and pick up in store. ... Web Design | Logo Design | Corporate Identity Design ...
    (uk.games.video.xbox)