Table fields, normalization

[Guest]

Hi - did a search on old posts regarding phone numbers and found the
following post from John Vinson:

I'd keep State in the main table (assuming only one address, in the
Address table otherwise). These days for phones you can pretty much
assume that *somebody* in the database will have at least two; all it
takes is one such person to make a separate PhoneNumbers table - as
suggested elsewhere in the thread - a formal necessity. There are
enough people in most any organization to make it a practical
necessity as well. Just have a table with EmployeeID, PhoneType, Phone
fields, and maybe a little lookup table (NOT lookup field...!)
PhoneTypes with a primary key text field containing "Work", "Home",
"Cell", "Pager" and whatever other values turn up.

John W. Vinson[MVP]

I'm trying to reconfigure my tables - actually a version 2 of the whole
database, because I have LOTS of blank fields in my contacts table.

Old Table Fields:
ContactID
Prefix (Mr., Dr. etc.) looks up a value list/row source.
What do you mean by "a little lookup table (NOT lookup field...!)"
above. Should I have tblPrefix???
FirstName;MI;LastName;Suffix (text box, no lookup)
Spouse
Address1 . . etc
Country (see below)
Phone
email
company
coAddr1...etc
EntryDate; DateModified; MemberSince; LastParticipatedDate
Comment1,2
Deceased

And this is the improved version! So I'm working on improving / normalizing
this into multiple tables. For example, I have 4000 contacts, only 150 of
which have company information. And a few have more than one phone # - it
gets stuck in the comment field. So how does this table structure look:

tblContact (name info)
tblAddresses (home, work, summer, winter) How to note which is primary?
tblPhoneNumbers (as in the original old post)
tblPrefixes (what about 'not in list' items?)
tblCountry (of 4000+ contacts, only 8 are outside the US. We're a non-profit
& don't want to pay extra postage!)

Also tblPayments to track membership & other $$
tblStatuses for (in)Active member; Deceased; Board; Staff etc.
as well as other specialized tables that I'm not messing with just now . . .

Your advise is appreciated - TIA!

Regards, LIsa

 

[John Vinson]

I'm trying to reconfigure my tables - actually a version 2 of the whole
database, because I have LOTS of blank fields in my contacts table.

Old Table Fields:
ContactID
Prefix (Mr., Dr. etc.) looks up a value list/row source.
What do you mean by "a little lookup table (NOT lookup field...!)"
above. Should I have tblPrefix???

Yes, or you can use a List Of Values combo box on a Form. Don't use a
combo box in any table datasheet (that's what I meant by "lookup
field").

FirstName;MI;LastName;Suffix (text box, no lookup)
Spouse
Address1 . . etc
Country (see below)
Phone
email
company
coAddr1...etc
EntryDate; DateModified; MemberSince; LastParticipatedDate
Comment1,2
Deceased

And this is the improved version! So I'm working on improving / normalizing
this into multiple tables. For example, I have 4000 contacts, only 150 of
which have company information. And a few have more than one phone # - it
gets stuck in the comment field. So how does this table structure look:

tblContact (name info)
tblAddresses (home, work, summer, winter) How to note which is primary?

Probably a Yes/No field [Primary].

tblPhoneNumbers (as in the original old post)
tblPrefixes (what about 'not in list' items?)

That's just a convenience, really. You might just want to leave Limit
to List set to False so you can insert "Sri" or "Her Royal Highness"
or whatever prefixes you need occasionally but not routinely.

tblCountry (of 4000+ contacts, only 8 are outside the US. We're a non-profit
& don't want to pay extra postage!)

Also tblPayments to track membership & other $$

You may want a PaymentTypes table

tblStatuses for (in)Active member; Deceased; Board; Staff etc.

Well... someone could be an active member and also on the Board could
they not?

as well as other specialized tables that I'm not messing with just now . . .

You're heading in the right direction. You can run "Normalizing Union
Queries" to populate the phone and address tables if you wish - post
back if you need help, or do a GoogleGroups search using that term.

John W. Vinson[MVP]