Need help with normalization problem.

[Sue]

Hello. I am trying to create a contact management database. I'm moving from
"one big table" to a more normalized structure. I would love some feedback
and I have a question or two. I have reproduced my tables below (in
excruciating detail!):

1. Comments, suggestions?
2. In the beginning, I included the business information in tblContacts. The
current structure pulls this out, but this isn't working because in some
instances, many contacts will work at one business with different addresses
(occasionally even different city & state), phone numbers, pagers, and
emails. But if I put the business information in tblContacts, there will be a
lot of duplication.
3. I know that if I have the Date of Birth, I can do calculations to
generate birthday greetings. I have a report which lists birthdays by month.
BUT I don't know the year of everyone's birth - so I can't really use the
Date of Birth. This led me to create multiple fields to deal with birthdays -
Is there a better way?

I know this is a lot, but I also know that I need to get the structure
right! Thanks very much for any help!

Sue...




tblContacts

ContactID (PK, Autonumber)
LastName
FirstName
Address1
Address2
City_ID (FK tblCity)
State/Province_ID (FK tblState/Province)
Zip_ID (FK tblZip)
Country_ID (FK tblCountry)
Phone
Cell
Fax
WebPage (Hyperlink)
Email
BrowserListAccess (Yes/No)
DempoID
DEA#
BDMonth_ID (FK tblMonth)
BDYear (Text)
BDCardList (Yes/No)
AnniversaryMonth_ID (FK tblMonth)
AnniversaryDate (Text)
AnniversaryYear (Text)
AnniversaryCardList (Y/N)
ContactType_ID (FK tblContactType)
Comments (Memo)
Business_ID (FK tblBusiness)
Protocol_ID (FK tblProtocol)

Lookup Tables:

tblCity (CityID, City)
tblState/Province..
tblZip...
tblCountry...
Protocol_ID...
tblBusiness_ID (BusinessID, Address, Phone, Fax, etc.)

 

[Amy Blankenship]

Hello. I am trying to create a contact management database. I'm moving
from
"one big table" to a more normalized structure. I would love some feedback
and I have a question or two. I have reproduced my tables below (in
excruciating detail!):

1. Comments, suggestions?
2. In the beginning, I included the business information in tblContacts.
The
current structure pulls this out, but this isn't working because in some
instances, many contacts will work at one business with different
addresses
(occasionally even different city & state), phone numbers, pagers, and
emails. But if I put the business information in tblContacts, there will
be a
lot of duplication.
3. I know that if I have the Date of Birth, I can do calculations to
generate birthday greetings. I have a report which lists birthdays by
month.
BUT I don't know the year of everyone's birth - so I can't really use the
Date of Birth. This led me to create multiple fields to deal with
birthdays -
Is there a better way?

I know this is a lot, but I also know that I need to get the structure
right! Thanks very much for any help!

Sue...




tblContacts

ContactID (PK, Autonumber)
LastName
FirstName
Address1
Address2
City_ID (FK tblCity)
State/Province_ID (FK tblState/Province)
Zip_ID (FK tblZip)
Country_ID (FK tblCountry)
Phone
Cell
Fax
WebPage (Hyperlink)
Email
BrowserListAccess (Yes/No)
DempoID
DEA#
BDMonth_ID (FK tblMonth)
BDYear (Text)
BDCardList (Yes/No)
AnniversaryMonth_ID (FK tblMonth)
AnniversaryDate (Text)
AnniversaryYear (Text)
AnniversaryCardList (Y/N)
ContactType_ID (FK tblContactType)
Comments (Memo)
Business_ID (FK tblBusiness)
Protocol_ID (FK tblProtocol)

I wouldn't put city, and zip into lookup tables. State is ok if you
actually want to use it to help people enter the data, like with a Combobox
or something. Why does the web page belong to the contact and not the
business?

Lookup Tables:

tblCity (CityID, City)
tblState/Province..
tblZip...
tblCountry...
Protocol_ID...
tblBusiness_ID (BusinessID, Address, Phone, Fax, etc.)

Let's try something like this:

tblContact
ContactID
FirstName
LastName
ContactTypeID
LocationID
(there are soome other things there that I don't understand, so they may
belong here too)

tblContactType
ContactTypeID
ContactTypeName

tblEvent
EventID
ContactID
EventType
EventDate
SendCard

tblEventType
EventTypeID
EventTypeDesc
CardToSend

tblBusiness
BusinessID
BusinessName

tblLocation
BusinessID
LocationDesc
StreetAdd1
StreetAdd2
City
StateProvince
ZipPostal

tblLocationPhone
LocationPhoneID
PhoneType
PhoneNumber

tblContactPhone
ContactPhoneID
PhoneType
PhoneNumber

tblPhoneType
PhoneTypeID
PhoneTypeDesc

I think you could probably consolidate LocationPhone and ContactPhone
somehow. For instance, you could create a contact that was the default for
a location but wasn't any specific person and wouldn't have any events
associated with it.

HTH;

Amy