Hi, this is Crystal
Welcome to the last plannning lesson
before we go into Access.
At the heart of most databases is
a need to keep track of contact information.
I am going to show you, step-by-step,
how to design a Contact Management System
that can be used on its own or slide into another application.
Original music by Mark Davis.
For more great tutorials, visit our sponsor
www.EverythingAccess.com
There are real people and artificial people.
Real people are Humans.
Artificial people are companies, or organizations.
Both have need to keep track of the same kinds of data.
The Primary Information is
Addresses, Phone Numbers,
Email Addresses, and Websites.
The Secondary Information is
Notes, Lists, Events, and Photos.
Today, we are going to plan the primary tables.
Our People table needs to be flexible enough
to accomodate humans and companies.
The first thing we need is
a place to keep track of the Primary and Secondary names.
NamPri will be the Last Name for a Human
or the Company name for a Company.
NamSec will be the First Name for a human
and, if applicable,
the Division (or DBA or Subsidiary) for a Company.
The Primary key of this table will be
an Autonumber field called PID.
To make the records easier to filter,
we will include a Category.
The Categories will be defined in a separate table.
In the People table, we will store
a Long Integer CatID as a Foreign Key.
Because we have Humans and Companies
both stored in this table,
we need a way to differentiate each record;
so, if an entity is Human,
we can prompt for additional Human Fields.
IsHuman will be a Yes/No field.
It will be Yes if the record is a human.
It will be No if the record is an artificial entity.
If the record is a Human entity,
we will keep track of additional information:
Middle Name,
Salutation such as 'Mr.', 'Ms.', or 'Dr.',
Suffix such as 'Jr.' or 'III',
Nick Name, Gender
Date of Birth will be tracked in 3 numeric fields
instead of one date field.
Do you remember WHY we are going to do this?
How many people do you know the month and day
of a birthday, but not the year?
By tracking this information in 3 fields,
we can enter just what we know;
and it can always be combined when need an actual date.
CatID is a Foreign Key in the People table
CatID is the Primary Key in the Categories table
For each one category, such as Friend,
there can be many People.
There is a one-to-many relationship
between CatID in the Categories table
to the CatID in the People table.
The Phones table has
an Autonumber Primary Key named PhoneID.
The PID field is a foreign key to People
so we know who the number belongs to.
We can use the TypIDpho
to determine what type of phone this is
such as a Home phone or a Work number,
a Fax line, or a Cell phone.
The Phone field holds the actual phone number.
In case there is an extension, we have a phoExt field.
phoNote is for a short note.
People can have more than one Phone Number.
Therefore, a one-to-many relationship
exists between People and Phones.
One Phone Type can be used for many Phone records.
The eMail table
has an Autonumber Primary Key named EmailID
The PID field is a foreign key to People
so we know who the email address belongs to.
We can use the TypIDema to specify a Type such as
Home, Work, Primary, or Alternate.
The Email field is for the eMail address.
emaNote is for a short note.
People can have more than one eMail Address.
There is a one-to-many relationship
between People and eMail.
There is also a one-to-many relationship
between Email Types and Email.
The Websites table has an
Autonumber Primary Key named WebID.
The PID field is a foreign key to People
so we know who the web address address belongs to.
TypIDweb specifies a Type such as
Personal, Work, Blog, or Reference.
The URL field is for the web address.
webNote is for a short note.
URL stands for Uniform Resource Locator.
The URL is what displays in the address bar of a browser.
People can have more than one Website
so there is a one-to-many relationship
between People and Websites
There is also a one-to-many relationship
between Web Types and Websites.
Now comes Addresses…
If you live in the United States of America,
addresses are fairly standard.
The same cannot be said for other parts of the world.
Therefore, I will show you
how to design a flexible structure.
The Addresses table has
an Autonumber Primary Key named AdrID
TypIDadr specifies a Type for the building such as
Residence, Business, or School.
Addr1 is the first address line.
Addr2 is for the second address line.
The City will be stored in a field called City.
St is for the State or Province abbreviation.
The Zip or Postal code will be stored in Zip.
Ctry is for the standard 2-character Country abbreviation.
Some of you have a 2-part Postal code…
as do we here in United States as well –
but we do not normally think of it as 2 parts.
And some people only know
the first 5 digits of their zip code.
It is handy to be able to filter by this,
so we will add another field for the Zip Code Extension.
We will call the second field Zip2.
Separating the extension also makes it easier to
format the field in case the extension is missing.
Some international addresses also do not show
the 2 parts of postal code together
– maybe there is one part before a city
and one part after a city.
Here is the revised structure showing Zip as 2 fields.
I have also added an Area field
that you can use to group addresses
by a particular area or region.
The problem with this structure is that it cannot accommodate
all of the international addresses.
This structure anticipates
up to 2 address lines before the city
… but that is not enough for everyone.
In our example database, we will stick with 2 lines -- for now.
For those of you who do need to track more
set up another table for Address Lines
so there can be one, two, three, … six!
… however many you want!
The Ctry field in the Addresses table
refers to the Country Code.
Standard Country Codes can be found
in my reference database.
Click (more info) on the right for a link.
There is a one-to-many relationship
between Countries and Addresses.
The St field is the State Abbreviation.
Also in my reference database is a table with all the
American, Canadian, and US Miltary States.
Click (more info) on the right for a link.
There is a one-to-many relationship
between States and Addresses.
The reason Ctry is also in Addresses is
because not everyone lives in the States.
The State may not be specified
and we will also use this field for other reasons.
There is a one-to-many relationship
between Address Types and Addresses.
Did we miss something?
There was no PID in the Addresses table…
How are we going to relate an address to a person?
An Address describes a location.
People can have more than one Address …
and Addresses can have more than one person.
A many-to-many relationship exists
between People and Addresses.
We need a Cross-Reference Table (or Junction Table)
to set this relationship properly.
The Junction table will be called PAdr.
There is a one-to-many relationship
between People and PAdr.
There is a one-to-many relationship
between Addresses and PAdr.
The result is a many-many relationship
between People and Addresses.
The way we USE an address also has a Type such as
Billing, Shipping, Home …
so we will add a Types field to our PAdr table.
For each of our related tables
there is a Type
and the Types tables have the same structure:
an Autonumber Primary Key
and a descriptive text field
Rather than creating 5 tables with the same structure,
we are going to use a generic Types table.
Here is our generic Types table structure:
TypID will be an Autonumber Primary Key
Typ, without the "e", is a text field.
The reason we take the "e" off is
because T-Y-P-E is a reserved word.
Each of the tables in our system
will be assigned a unique number.
TID stands for Table ID
TID will be stored in Types to provide
a way to know which records
correlate to other specific tables.
Another thing we are going to do is
preface each of the contact tables with "c_"
This will keep the contact tables grouped together by name
in case you decide to
incorporate them into a larger application.
Here are the TIDs we will use for each table:
100 for People, 105 for Addresses, 110 for Phones,
115 for eMail Addresses, 120 for Websites
Each time we create a table,
we will also create a TID for it.
TIDs will also be used for another feature: "Anywhere Notes"
Once we get our main tables created,
make some forms and put some data in,
we will discuss and set up "Anywhere Notes"
What's Next?
We will be going into Access to
Create tables, Define Fields and Properties,
Drag Relationships, and Make sample records