Table Design/Classification

[yamefui]

Hello, Access newbie seeking expert advice. I have listed the fields I
currently have in one table and would like to obtain advice on how to split
out the fields into other tables.

This is meant for a BUSINESS CONTACT database for myself (I don't want to
use the Access template dbase) and I will be creating a data input form as
well as some basic queries and reports. I am using the MS Access Bible 2003
by Prague and Irwin but I'm really confusing myself on table
separation/relationships after reading a lot of the discussion group opinions
on normalization. Thank you for any direction you can offer. Here is what I
have in my current table:

idnContactID(Autonumber)
chrCompanyName(text)
hlkCompanyWebsite(hyperlink)

chrCategory(text) (I have a separate table for classifying business contacts
by profession for this field because I have a limited number of professional
contacts who work in certain employment fields. My form will have a combo box
here)

chrCategoryHomeSubdivision(text) (I have a separate table for Homeowner
subdivsion classifcation as there are at least 10. My form will have a combo
box here)

chrTitle(text) (i.e., doctor, lawyer, real estate agent...too many for
another table)
chrFirstName(text)
chrLastName(text)
chrNameSuffix(text) (i.e. jr, sr, etc....too many for another table)

NOTE: I have three different types of address categories because the people
I do business with USE all three address options (Business, Personal or an
Alternate address)

chrBusinessAddress(text)
chrBusinessCity(text)
chrBusinessState(text) (I have a separate state table for state
abbreviations that I use in a combo box in a form)
chrBusinessZipCode(text)

chrPersonalAddress(text)
chrPersonalCity(text)
chrPersonalState(text)(I have a separate state table for state abbreviations
that I use in a combo box in a form)
chrPersonalZipCode(text)

chrAlternateAddress(text)
chrAlternateCity(text)
chrAlternateState(text)(I have a separate state table for state
abbreviations that I use in a combo box in a form)
chrAlternateZipCode(text)

chrBusinessPhone(text)
chrBusinessPhoneExtension(text)
chrMobilePhone(text)
chrPager(text)
chrFaxNumber(text)
chrEmailAddress(text)
chrAlternativeEmailAddress(text)
memNotes(memo)

 

[Smartin]

Comments inline

Hello, Access newbie seeking expert advice. I have listed the fields I
currently have in one table and would like to obtain advice on how to split
out the fields into other tables.

This is meant for a BUSINESS CONTACT database for myself (I don't want to
use the Access template dbase) and I will be creating a data input form as
well as some basic queries and reports. I am using the MS Access Bible 2003
by Prague and Irwin but I'm really confusing myself on table
separation/relationships after reading a lot of the discussion group opinions
on normalization. Thank you for any direction you can offer. Here is what I
have in my current table:

idnContactID(Autonumber)
chrCompanyName(text)
hlkCompanyWebsite(hyperlink)

chrCategory(text) (I have a separate table for classifying business contacts
by profession for this field because I have a limited number of professional
contacts who work in certain employment fields. My form will have a combo box
here)

chrCategoryHomeSubdivision(text) (I have a separate table for Homeowner
subdivsion classifcation as there are at least 10. My form will have a combo
box here)

chrTitle(text) (i.e., doctor, lawyer, real estate agent...too many for
another table)
chrFirstName(text)
chrLastName(text)
chrNameSuffix(text) (i.e. jr, sr, etc....too many for another table)

NOTE: I have three different types of address categories because the people
I do business with USE all three address options (Business, Personal or an
Alternate address)

This looks OK up to this point. What lies below breaks normalization
because you have so many repeating fields. To quote the sage
contributors here, "rows are cheap, columns are expensive". Remove these
columns from your contacts table, and make a new, related table with
only the generic contact elements needed. E.g.,

PK
FK to the contacts table
type identifier (e.g., business, personal, alternate, etc.)
Address
City
State
etc.

This way, you only create a record of contact information where it is
relevant. Moreover, the two-table structure allows you to easily create
new types of contacts willy-nilly, without altering your table structure.

You might have a case to break out the phone numbers to yet a third
table, but I suspect that may be overkill.

 

[Allen Browne]

I take it you currently have all those fields in the one table?

There are a couple of issues here. First is the repeating addreess fields.
You might like to consider creating a separate table to hold the addresses.
The fields will be similar to what you have, with a couple of extra fields:
AddressTypeID Foreign key to a table of types (business, personal,
....
Priority Number
The Prioritity is optional, but allows you to specify which is the preferred
address for that type. You can then create a subquery to find an address to
use for postage, and the subquery can ORDER BY by whether the addresstype is
postal, and the priority, still returning an address if there is no postal
address.

The other (more important) issue is that it seems to me that one company
could contain many people. The way you have it set up, each person in the
table has the chrCompanyName typed in, so a slight misspelling will fail to
match the people as belonging to the same company.

A typical solution for this is to create a separate table of companies, with
a one-to-many relation to the persons table. That works, but you now need
separate searches to find companies and persons, since they are in different
tables.

An alternative solution is to create separate records for the companies in
the *same* table, and then join the table to itself to indicate which
persons belong to companies.

An extension of that alternative approach is to permit other kinds of
groupings of people than just employess of a company. People belong to
things like households, committees, clubs, task forces, and so on. If you
might want to handle those kinds of groupings as well, there's an example
database you can download here that illustrates how that structure might
work:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[yamefui]

Allen and SMartin. Thank you. Allen, I currently have all this information in
one table except for the Lookup tables that I noted.

SMartin, question regarding the separation of the aforementioned address
fields (address, city, state, zipcode) and address types (business, personal,
alternate) from my current table to a separate table...

1. What if I have access to a spreadsheet with approx. 5,000 names and
address information of real estate agents that has name, address, city,
state, zipcode, phone info. (an option that I could have repeatedly from a
provider in the future)? If I did not want to key that information in via
form and instead wanted to set up the spreadsheet with the exact same fields
as my Access table in order to import it into the table, would that be
considered a legit. reason to keep the address information in the primary
table that I currently have?

Essentially, if I split the address information away from my primary table I
can't really import all of the agent information directly into the table,
correct? Or is there a means available to circumvent that if I use two tables
as you suggest (contact info. and address info.)?
My original idea was to keep all the fields/records I need in one table in
order to facilitate importing spreadsheets with high volumes of client
contact information (so I could avoid the keying...and I know I would be
relying on the input integrity of another person(s).

2. The challenge to this process for me is that if I use the import method,
I believe I would have to run a duplicates query in order to weed out the
duplicate agent name information from subsequent spreadsheet import updates.
There is a high turnover ratio of agents (20%) so when I get a new
spreadsheet source I have to be able to incorporate new agent name and
information while ensuring I don't have repeat/duplicate agent name and
address information in the table.

3. If I were to use the import process, would Access still automatically
assign the new record entries with autonumbers?

Did I phrase my questions understandably or have I taken one too many
'stupid' pills today? Thank you.

 

[Smartin]

More responses inline...

Allen and SMartin. Thank you. Allen, I currently have all this information in
one table except for the Lookup tables that I noted.

SMartin, question regarding the separation of the aforementioned address
fields (address, city, state, zipcode) and address types (business, personal,
alternate) from my current table to a separate table...

1. What if I have access to a spreadsheet with approx. 5,000 names and
address information of real estate agents that has name, address, city,
state, zipcode, phone info. (an option that I could have repeatedly from a
provider in the future)? If I did not want to key that information in via
form and instead wanted to set up the spreadsheet with the exact same fields
as my Access table in order to import it into the table, would that be
considered a legit. reason to keep the address information in the primary
table that I currently have?

I wouldn't call it a "legitimate" reason, but since you are working with
someone else's data it would seem you are obliged to at least start with
the flat structure.

In case I failed to mention previously, in lieu of importing, you can
use Access to link to the spreadsheet and treat it as if it was a table
in the database. Then you can go to work figuring out how to get the
data in the right places.

Essentially, if I split the address information away from my primary table I
can't really import all of the agent information directly into the table,
correct? Or is there a means available to circumvent that if I use two tables
as you suggest (contact info. and address info.)?

No you can't import directly and end up with a normalized structure, but
it might be possible to decompose that flat file into something that
resembles a database (rather than a spreadsheet).

My original idea was to keep all the fields/records I need in one table in
order to facilitate importing spreadsheets with high volumes of client
contact information (so I could avoid the keying...and I know I would be
relying on the input integrity of another person(s).

Again, I think it may be possible to make the data conform to your
specifications, but without seeing what you've got it's impossible to
say exactly how.

2. The challenge to this process for me is that if I use the import method,
I believe I would have to run a duplicates query in order to weed out the
duplicate agent name information from subsequent spreadsheet import updates.
There is a high turnover ratio of agents (20%) so when I get a new
spreadsheet source I have to be able to incorporate new agent name and
information while ensuring I don't have repeat/duplicate agent name and
address information in the table.

The challenge of dealing with dups will be there no matter what. To be
proactive, define your table(s) to disallow duplicates based on your
definition of "duplicate".

3. If I were to use the import process, would Access still automatically
assign the new record entries with autonumbers?

It's your option. You can elect to choose your own PK, none, or allow
Access to add one. The latter will be autonumber.

Did I phrase my questions understandably or have I taken one too many
'stupid' pills today? Thank you.

Hope this helps!