Prospective Client Tracking Database
Thank you for the feedback John. That all makes sense!
"John W. Vinson" wrote:
On Tue, 19 Jan 2010 12:21:01 -0800, TGC wrote:
I am new to Access 2007 and recently reviewed all the online tutorials on the
Microsoft website and reviewed the provided templates (none of which fit my
needs). I understand the general concept of tables, forms, queries, reports,
etc. and have created tables and attempted to create a form for my data, but
am a little unsure about my design and was hoping for some feedback from
others that know Access better than I (not hard to accomplish that
distinction, btw!).
I want to create a prospective client tracking database for a commercial
landscape company. I am looking to collect information on companies, their
contact people, and the properties they manage. One company, for example,
may have multiple contact people and those contact people may manage multiple
properties. Within those 3 main areas (companies, contacts, properties)
there are a bunch of fields relating to the area (phone, email, address,
etc.).
I started by creating 3 tables (companies, contacts, properties) and then
created relationships amongst the tables be relating the contacts' first and
last name with the companies table, relating the properties' street address
and the companies' name with the contacts table, and relating the contacts'
first and late name with the properties table.
I then created a form with the form wizard that had the companies table as
the main form and the contacts and properties tables as the subforms.
Am I going about this the right way? All I want to be able to do is track
the companies and be able to add multiple contacts and multiple properties
within the companies.
Thank you in advance for your help!
I would recommend at least some changes. Your basic table structures look ok
for a start... but!
Names are NOT good keys or linking fields. A Key should meet three
requirements: it *must* be unique; it should be stable, not changing over
time; and ideally it should be short. People's names fail on all three counts!
The Primary Key of your Companies table should be a numeric, meaningless
unique identifier, such as an Autonumber (CompanyID let's call it). This would
be related to a Long Integer CompanyID in the Contacts table. Similarly, each
Contact should have a numeric ContactID; otherwise you might have Dale
Williams, the big husky guy, and Dale Williams, the nice grey-haired lady,
getting their properties mixed up.
You also need to be sure that your relationships reflect the business logic.
Is a Property in fact managed *by an individual contact* or by a company?
Might a property have more than one person who you might need to contact?
Also be sure you're not storing data redundantly. Phone, email, address need
to be in the table for the entity (person or company) that uses them, and
should NOT be duplicated in any other table; a property probably does not have
an email address, for example.
Note also - you may be doing this correctly, but it wasn't clear - that the
"foreign key" goes in the Many table. That is, there should be a CompanyID in
the Contacts table, but there should not be any information about the Contact
in the Companies table.
--
John W. Vinson [MVP]
.
|