Claudio_Martin said:
I am very very new to access and I am trying to create a DB from an Address
Book. My question is:
Is it better to create one Address Book table with several address types
(customer, vendor, employee, etc). Then create a Contact Type Table to
specify the diffferent contacts (names, last names and ph#s) for the same
address and then individual tables for customers, vendors, etc. Or create
these tables directly without creating the Address book and contact type
table....I'm confused....
If you have a personal address book of friends and family, some of those
contacts may share an address. If, say, a married couple move, and
you have separate contact entries for them (more likely in the age of
mobile phones) then you'll probably end up having to change the address
on both entries separately.
If you're dealing with business contacts you may have hundreds of people
at one location, differing only in, say, room number. At that level it
becomes clearer that you need a "location" table containing the address,
and you'd include a reference (a foreign key) to the relevant location
in each contact item. If there's an office move, then you'll only need
to change one address in the whole database, and if some folk move to a
different office you simply change the reference in their individual
entry in the Contacts table.
I do think you want to have all your contacts in one table, as this
makes searching so much easier. You can add a field to the contact item
to show contact type.
It gets a little more complicated if you need to store different
information for the different contact types. You'd probably have very
different information about employees than you could for customers, for
example. In this case you can have a separate table of Employee details
(grade, salary, appraisal-rating, whatever) which contained a reference
(foreign key) to the Name and Address details in the Contacts list.
You'd set something similar up for vendors and for customers, but you'd
still be able to look up a mobile phone number, room number or email
address for any of them with the same query. If you need the full
address you use a query to "Join" information from more than one table.
Now, depending on how complicated your situation is, you might need to
have other information groupings set up as tables. If, for example, you
are a megga-corporation where salary depended directly on grade and
appraisal then you'd record that "rule" as a table which linked grade,
appraisal and salary. For an individual, you'd traverse the link from
the contact table to the employee table to get the grade and appraisal
score, and on to the "remuneration" table to get the salary. A bit
contrived, but I hope you get the idea.
This is confusing at first (even when well-explained!) but it does
become clearer. Have a look at these:
Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)
Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial
Table design is critical - get it right and everything is SO much easier!
Phil, London
