Normalizing

[Lars Brownies]

I'm further experimenting with normalization, not finding it easy. I have
these 3 tables:

tblPerson
ID_person*
LastName
FirstName
Etc.

tblPhone
ID_phone*
Phonenumber
BusinessPrivate
MobileStandard

tblPersonPhone
ID_person*
ID_phone*

First of all I find it hard to think of meaningful names for the category
fields BusinessPrivate and MobileStandard, and if I should make Yes/No
fields of them, since both fields can only have 2 values. Secondly, I want
to add emailaddresses to the database. Should I change the tblPhone table to
tblCommunication and field Phonenumber to Communication_name, as shown
below? So that I can store phonenumber and emailaddresses in the same field?
But MobileStandard is not an applicable field for emailaddresses which makes
me think email addresses should be in a separate table. Further, if I want
to run queries for emailaddresses I always have to exclude the phonenumber.
I'm now thinking I need a different table for email addresses.

tblCommunication
ID_communication*
Type
Name
BusinessPrivate
MobileStandard

Can someone give me some advice on this one?

Thanks,

Lars

 

[Jeff Boyce]

Lars

Only you know the real-world situation you are dealing with. What we offer
is based on the real-worlds we inhabit...

Here's something that solved a problem my agency was faced with ... and it
is overkill, pure and simple, for most needs!


Each person can have zero, one or more phone numbers.

Each phone number could be used by more than one person.

Each phone number is of a 'type' (e.g., POTS, mobile, FAX, ...).


Given these relationships in the (my) real world, I came up with:

tblPerson
PersonID
LName
...

tblPhone
PhoneID
AreaCode
PhoneNumber
Extension
PhoneTypeID

tlkpPhoneType
PhoneTYpeID
PhoneType (e.g., POTS, FAX, ...)

trelPersonPhone
PersonPhoneID
PersonID
PhoneID


NOTE: this approach was also used to handle the fact that each person could
have more than one address and each address could be used by more than one
person.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Beetle]

It may be legitimate to have a many-to-many relationship
between tblPerson and tblPhones if a person can have more
than one phone and a given phone can be assigned to more
than one person over the course of time. However, you would
not (I wouldn't think) want to have the same phone assigned to
more than one person at the same time. so you would want an
additional field or two in your junction table to indicate which
person a phone is currently assigned to.

As far as the phone category (BusinessPrivate, MobileStandard)
I would use another table (tblPhoneTypes) related 1:m to
tblPhones.

In regards to e-mail, they are usually person specific. In your
case, if a person can only have one e-mail address, you could
just add an email field to tblPerson. If they can have more than
one, use another table (tblEMails) that is related to tblPerson.

 

[Biz Enhancer]

Is there any reason why you need to separate email addresses and phone
numbers in to separate tables?

Why can you not have the following:
tblPerson
ID_person*
LastName
FirstName
Etc.

Tblcommstype
ID_Commtype
Commstype
'this table contains a list such as "Mobile", "Home Phone", "Work Email",
"Home Email" etc

TblCommsperson
ID_Comsper
ID_Person
ID_Commtype
commsdata (textfield) 'phone numbers, email, etc

That way you can simply pull all person related info into a list box on a form

HTH
Nick.

 

[Lars Brownies]

Thanks all for input which gave me some new ideas like the 'Primary' field.
I will dig into this further this weekend.

Lars

Lars:

As an example this is the model I used recently for sales staff phone
numbers
in one database:

SalesPersons
..SalesPersonID
..FirstName
..Lastname
..etc

SalesPersonPhoneNumbers
..SalespersonID
...PhoneNumber
..Extension
..PhoneType
..Primary

PhoneTypes
..PhoneType

In this database the PhoneTypes are LandLine, CellPhone and Fax. The
Primary
column in the SalesPersonPhoneNumbers table is a Boolean (Yes/No) column
and
indicates which is the primary contact number of each phone type for the
salesperson in question.

The interface is a salespersons form with subforms for each phone type, so
the RecordSource for the landlines subform for example is:

SELECT *
FROM SalespersonPhoneNumbers
WHERE PhoneType ="Landline"
ORDER BY IIf([Primary],0,1);

This puts the primary landline number first in the subform. Setting or
changing the primary number is controlled by code in the subform's module
like so:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

Dim strCriteria As String

strCriteria = "SalespersonID = " & Me.SalesPersonID & _
" And PhoneType = ""LandLine"""

' make this primary landline number if none already exists
' for this salesperson
If IsNull(DLookup("SalespersonID", "SalespersonPhoneNumbers",
strCriteria)
) Then
Me.Primary = True
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub


Private Sub Primary_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

Const conMESSAGE = _
"Another number is set as primary landline " & _
"number for this salesperson. Do you wish to replace " & _
"it with this number?"

Dim dbs As DAO.Database
Dim strCriteria As String
Dim strSQL As String

strCriteria = "SalespersonID = " & Me.SalesPersonID & _
" And PhoneType = ""LandLine"" And Primary = True"

' get user confirmation to change primary landline
' number for this salesperson
If Me.Primary Then
If Not IsNull(DLookup("salespersonID", "SalespersonPhoneNumbers",
strCriteria)) Then
If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Warning") = vbYes
Then
' set all other landline numbers for this salesperson to
non-
primary
strSQL = "UPDATE SalespersonPhoneNumbers " & _
"SET Primary = FALSE WHERE SalespersonID = " & Me.
SalesPersonID & _
" And PhoneType = ""LandLine"""
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
Me.Refresh
Else
Cancel = True
End If
End If
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Ken Sheridan
Stafford, England

I'm further experimenting with normalization, not finding it easy. I have
these 3 tables:

tblPerson
ID_person*
LastName
FirstName
Etc.

tblPhone
ID_phone*
Phonenumber
BusinessPrivate
MobileStandard

tblPersonPhone
ID_person*
ID_phone*

First of all I find it hard to think of meaningful names for the category
fields BusinessPrivate and MobileStandard, and if I should make Yes/No
fields of them, since both fields can only have 2 values. Secondly, I want
to add emailaddresses to the database. Should I change the tblPhone table
to
tblCommunication and field Phonenumber to Communication_name, as shown
below? So that I can store phonenumber and emailaddresses in the same
field?
But MobileStandard is not an applicable field for emailaddresses which
makes
me think email addresses should be in a separate table. Further, if I want
to run queries for emailaddresses I always have to exclude the
phonenumber.
I'm now thinking I need a different table for email addresses.

tblCommunication
ID_communication*
Type
Name
BusinessPrivate
MobileStandard

Can someone give me some advice on this one?

Thanks,

Lars