Table fields, normalization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LReber - 29 Dec 2004 20:13 GMT

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

Reply to this Message

John Vinson - 30 Dec 2004 01:03 GMT

>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.

ah, the old familiar "normalizing the wide-flat table" gambit... <g>

>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
[quoted text clipped - 15 lines]
>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!)

One record is enough to require the table <g>

>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]

Reply to this Message