Contact database

[pobnospam]

I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?

How would I handle spouses and there children ?

I tried a rough sketch of possible tables below.

Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName

Table2
PhoneTypeID
PhoneType (Landline,work, etc)

Table3
PhoneID
PhoneTypeID
PhoneNumner

Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)

Table5
IndvidualID
PhoneID
PhomeTypeID

Table6
FamilyID
FamilyName

[ManningFan]

I think you can get rid fo Table 5, it seems redundant.

I would just add a table with Individual ID (or FamilyID, depending on
how your data actually works), AddressID and PhoneID. You should be
able to link everything else the way you have your database set up.

Table 1 needs some work, you're going to have multiple entries there
that you can avoid. Just use FamilyID, IndividualID, FName and LName.
You can link the phone number and address to the individual via the new
table mentioned above.
po*******@gmail.com wrote:

I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?

How would I handle spouses and there children ?

I tried a rough sketch of possible tables below.

Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName

Table2
PhoneTypeID
PhoneType (Landline,work, etc)

Table3
PhoneID
PhoneTypeID
PhoneNumner

Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)

Table5
IndvidualID
PhoneID
PhomeTypeID

Table6
FamilyID
FamilyName

[chris.nebinger]

Just a personal pet-peeve:
In tables like Table2 and Table4, why have an AutoNumber on a field
that should be unique? Why not just:

Table2:
PhoneType (PK)
Table4:
EmailType (PK)

This way you are avoiding having to include the reference table every
time you want to find out the PhoneTypeID 1 is Home.
Chris Nebinger


ManningFan wrote:

I think you can get rid fo Table 5, it seems redundant.

I would just add a table with Individual ID (or FamilyID, depending on
how your data actually works), AddressID and PhoneID. You should be
able to link everything else the way you have your database set up.

Table 1 needs some work, you're going to have multiple entries there
that you can avoid. Just use FamilyID, IndividualID, FName and LName.
You can link the phone number and address to the individual via the new
table mentioned above.
po*******@gmail.com wrote:

I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?

How would I handle spouses and there children ?

I tried a rough sketch of possible tables below.

Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName

Table2
PhoneTypeID
PhoneType (Landline,work, etc)

Table3
PhoneID
PhoneTypeID
PhoneNumner

Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)

Table5
IndvidualID
PhoneID
PhomeTypeID

Table6
FamilyID
FamilyName

[ManningFan]

I saw that but I let it slide, because some people feel more
comfortable with descriptive IDs (i.e. H for Home, W for Work, C for
Cell Phone, etc...). If you're looking at raw data and just one table
it makes it so you don't have to bring in the other table to figure out
what the code is.

It's one of those "To each his own" things I guess...

ch************@gmail.com wrote:

Just a personal pet-peeve:
In tables like Table2 and Table4, why have an AutoNumber on a field
that should be unique? Why not just:

Table2:
PhoneType (PK)
Table4:
EmailType (PK)

This way you are avoiding having to include the reference table every
time you want to find out the PhoneTypeID 1 is Home.
Chris Nebinger


ManningFan wrote:

I think you can get rid fo Table 5, it seems redundant.

I would just add a table with Individual ID (or FamilyID, depending on
how your data actually works), AddressID and PhoneID. You should be
able to link everything else the way you have your database set up.

Table 1 needs some work, you're going to have multiple entries there
that you can avoid. Just use FamilyID, IndividualID, FName and LName.
You can link the phone number and address to the individual via the new
table mentioned above.
po*******@gmail.com wrote:

I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?
>
How would I handle spouses and there children ?
>
I tried a rough sketch of possible tables below.
>
Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName
>
Table2
PhoneTypeID
PhoneType (Landline,work, etc)
>
Table3
PhoneID
PhoneTypeID
PhoneNumner
>
Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)
>
Table5
IndvidualID
PhoneID
PhomeTypeID
>
Table6
FamilyID
FamilyName

[chris.nebinger]

I just shudder when I see the PhoneTypeID field. I think of
AutoNumbers, then sending it out to two different people. One adds
Home, then Work (1 is home, 2 is work), the other adds Work then Home
(1 is work, 2 is home). The data then gets consolidated, and everyone
is complaining that the data is corrupt.

I hate autonumbers, and only use them when no other field is unique.
Chris Nebinger

ManningFan wrote:

I saw that but I let it slide, because some people feel more
comfortable with descriptive IDs (i.e. H for Home, W for Work, C for
Cell Phone, etc...). If you're looking at raw data and just one table
it makes it so you don't have to bring in the other table to figure out
what the code is.

It's one of those "To each his own" things I guess...

ch************@gmail.com wrote:

Just a personal pet-peeve:
In tables like Table2 and Table4, why have an AutoNumber on a field
that should be unique? Why not just:

Table2:
PhoneType (PK)
Table4:
EmailType (PK)

This way you are avoiding having to include the reference table every
time you want to find out the PhoneTypeID 1 is Home.
Chris Nebinger


ManningFan wrote:

I think you can get rid fo Table 5, it seems redundant.
>
I would just add a table with Individual ID (or FamilyID, depending on
how your data actually works), AddressID and PhoneID. You should be
able to link everything else the way you have your database set up.
>
Table 1 needs some work, you're going to have multiple entries there
that you can avoid. Just use FamilyID, IndividualID, FName and LName.
You can link the phone number and address to the individual via the new
table mentioned above.
>
>
po*******@gmail.com wrote:
I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?

How would I handle spouses and there children ?

I tried a rough sketch of possible tables below.

Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName

Table2
PhoneTypeID
PhoneType (Landline,work, etc)

Table3
PhoneID
PhoneTypeID
PhoneNumner

Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)

Table5
IndvidualID
PhoneID
PhomeTypeID

Table6
FamilyID
FamilyName

[paii, Ron]

I use the following, which allow unlimited references between contacts and
unlimited address and types of address per contact.

tblContact Primary contact list
ContactID PK
FirstName
LastName
Type (Person, Company ect)
....

tblContactLink Link together contacts from tblContact
ContactID FK
ContactID2 FK
Type (Self, Child, Work, Club ect)

tblContactAddress Primary address table
AddressID PK
ContactID FK
Type (Phone, Street, PO, email etc)
Location (Home, Work ect)
AddressInfo
....

<po*******@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

I am creating a contacts db for about 100+ people and I wiss to follow
normalization rules.
How would you suggest handling multiple addresses (home,
vacation,email, etc.) and phone numbers (landline,fax,mobile, work,
etc) ?

How would I handle spouses and there children ?

I tried a rough sketch of possible tables below.

Table1
FamilyID
IndividualID
AddressID
PhoneID
FName
LastName

Table2
PhoneTypeID
PhoneType (Landline,work, etc)

Table3
PhoneID
PhoneTypeID
PhoneNumner

Table4
EmailTypeID
EmailType (Work, Personal1,Personal2, etc.)

Table5
IndvidualID
PhoneID
PhomeTypeID

Table6
FamilyID
FamilyName