Is my design properly normalized?

[bluemoir]

I am designing a contact database for all our clients including all of their
employees

So far I have 4 Tables - Builder, Contacts, Department, Category

Category has 2 fields: CategoryID (Autonumber, Key) and Category (text) -
the 2 categories are 'office staff' and 'field staff'

Department has 2 fields: DeptID(Key) and Departments

Builder has 8 fields: BuilderID (Key) Buildername, phone, fax, address,
city, province, and postal code

Contacts has 14 fields: Firstname, lastname, email, directline, extention,
mobile, radio#, fax, pager, builder, category, department, job title, notes

The tables builder, department, and category are linked to contacts through
their key fields in one to many relationships to their respective fields in
contacts.

As of yet I have no key field in contacts. Should I add an autonumber field
and make it the key or make some other field the key?

Can I normalise this data further?

For curiosity sake what level of normalisation have I achieved?

Any answers would be fantastic, thanks in advance for your time.

 

[tina]

comments inline.

I am designing a contact database for all our clients including all of their
employees

So far I have 4 Tables - Builder, Contacts, Department, Category

Category has 2 fields: CategoryID (Autonumber, Key) and Category (text) -
the 2 categories are 'office staff' and 'field staff'

Department has 2 fields: DeptID(Key) and Departments

Builder has 8 fields: BuilderID (Key) Buildername, phone, fax, address,
city, province, and postal code

you might want to use two Address fields, Address1 and Address2. so that
addresses with Suite, Building, etc could be displayed/printed as (for
example):

52 Baker Street
Suite 105

that would be normal for U.S. addresses, but of course it may not be
appropriate in your country.

putting the company's phone number and fax number in separate fields
violates normalization rules by putting data (phone number type) in field
names ("phone" and "fax"). i might do it anyway, if no company would *ever*
have more than one of each, and if most companies have both. otherwise, i'd
probably move the phone numbers into a child table, and add a separate table
of phone types for ease of data entry and maintenance, as

tblPhoneTypes
TypeID (primary key)
TypeName

tblBuilderPhones
BPhoneID (k)
TypeID (foreign key from tblPhoneTypes)
PhoneNumber

Contacts has 14 fields: Firstname, lastname, email, directline, extention,
mobile, radio#, fax, pager, builder, category, department, job title,

notes

here, you have so many types of phone numbers that i would definitely move
them into a separate child table that utilizes the tblPhoneTypes described
above, as

tblContactPhones
CPhoneID (pk)
TypeID (fk from tblPhoneTypes)
PhoneNumber
Extension
Comments (this field isn't necessary, but i usually include it when the data
is related to persons - rather than companies - because it's handy to
specify additional information about a number, such as "Call this number
first", "Best way to reach the person after 5 pm", "Personal cell number,
use only in an emergency", etc.)

personally, i usually use a more general "Communicatons" table that would
accommodate phone numbers AND email addresses, radio numbers, etc, for each
contact. but that's a bit tricky to make a good-looking and easy-to-use
display/data entry interface at the form level, so let's just leave those
two items of data in your Contacts table in this case.

The tables builder, department, and category are linked to contacts through
their key fields in one to many relationships to their respective fields in
contacts.

sounds fine.

As of yet I have no key field in contacts. Should I add an autonumber field
and make it the key or make some other field the key?

definitely include a primary key field in the Contacts table (with very rare
exceptions, *every* table in an Access database should have a primary key).
i didn't see a field that looked good for a natural key, so yes, i'd add an
Autonumber field to serve as a surrogate primary key.

Can I normalise this data further?

For curiosity sake what level of normalisation have I achieved?

Any answers would be fantastic, thanks in advance for your time.

hth

 

[bluemoir]

Thanks a ton - I've had no outside input on this and was feeling a little
shaky. Now I can move forward with more confidence. Much appreciated!

Ross

 

[tina]

you're very welcome! i was in your position often during my first years as a
developer; but you never need to feel alone, Ross - we're always here to
help. good luck!