Re: Table Design/Classification, What If ?#1
- From: yamefui <yamefui@xxxxxxxxx>
- Date: Sat, 30 Dec 2006 08:25:00 -0800
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.
--
jon
"Smartin" wrote:
Comments inline.
yamefui wrote:
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.
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
- References:
- Table Design/Classification
- From: yamefui
- Re: Table Design/Classification
- From: Smartin
- Table Design/Classification
- Prev by Date: Re: Multiple Product Description and Quantities?
- Next by Date: Re: Multiple Product Description and Quantities?
- Previous by thread: Re: Table Design/Classification
- Next by thread: Re: Table Design/Classification
- Index(es):
Relevant Pages
|
