Hi, this is Crystal.
Are you ready for more planning?
First, let's play a little pool…
(music by Ken Gaines)
For more great tutorials, visit our sponsor
EverythingAccess.com
We learned that the first step in planning a database
is to prepare.
Visualize … 'see' what you will keep track of.
What is it? What does it look like?
Organize
Follow Naming Guidelines
The time you spend up-front creating a solid structure with logic
will save you endless headaches down the road.
A discussion about naming would not be complete
without mentioning standard guidelines.
We have mainly discussed tables
because that is where you need to start.
Access has
Tables, Queries, Forms, Reports, Macros, and Modules
As we get further in our lessons, we will be covering each
-- in detail.
For now, realize they exist
and there are conventions for naming them.
The same guidelines that apply to fieldnames
are also applicable to table names.
Aside from the common sense guidelines
I have been giving you,
Leszynski and Reddick created a convention which is widely used.
They suggest descriptive tags
at the beginning of all object names
such as tables and queries.
If an object is a table, it will start with 'tbl'
In my databases, anything WITHOUT a prefix is a table.
I don't preface tables with 'tbl'
because it is more to type and mentally skip.
The Leszynski/Reddick convention uses 'qry' to denote a query
I use 'q_'
Leszynski/Reddick uses 'frm' to specify a form, I use 'f_'
Leszynski/Reddick uses 'rpt' for reports, I use 'r_'
Leszynski/Reddick uses 'mcr' for macros.
I don't normally use macros
except an AutoExec macro,
which AUTOmatically EXECutes when the database is opened.
Leszynski/Reddick uses 'bas' for modules, I do that too.
The more you research,
the better ideas you will get to create your own style.
I say one thing and others say something different …
Who is right? We all are
You need to decide what is best for YOU.
What you choose as a naming convention
is not as important as having one.
Naming is a personal preference and we all have our own logic.
It is recommended that each field in a database
have a unique fieldname
not just unique to the table (which Access makes you do),
but in the whole database.
The exception to this is in naming Key Fields …
I like to keep key field names the same in different tables,
it represents the same piece of information.
Why not put Address and Phone
in the same table with People?
Excellent question, Barbara.
Let me tell you about my Aunt Sally.
I don't have a picture to show you
but here are the flip-flops she left at my house
when she visited.
Aunt Sally lives in New York -- and still does … kind of.
A few years ago, she bought a beach house in Florida
to have a warm place to go for the winter,
so, she has two addresses.
If we moved address fields into the People table,
we would have to create two records for Aunt Sally.
Aunt Sally is one person,
she should have ONE record in the People table.
Another option is to create two sets of address fields.
While this might seem to solve the problem,
it creates another one.
If we want to print an address list,
we would have a bunch of empty fields
And we would have to look in two places for the address
And I would need THREE address sets
to accommodate my nephew.
This is called a Repeating Group
and is against the rules of normalization.
Using numbers in fieldnames often indicates
that the data is not structured correctly.
Repeating Groups make it harder to report data.
Another reason to store Addresses in a different table is because
some people in your database may live in the same house.
If an address is already in the database,
are you going to put it in again?
Wouldn’t it be better to just pick it and move on?
The same reasons to put addresses in a separate table
apply to phone numbers.
If you have fields called HomePhone, WorkPhone, and CellPhone,
there would not be enough places
for the FOUR cell numbers that my friend, Bill, has.
The People table is the heart of this database.
Lets think very carefully how we are going to set it up.
We touched on storing Humans and Companies together.
They're together in the phone book and that works well.
You can look a human up by their last name
and you can look up a company by the company name.
So let's create a field called NameMain.
Now, humans have a lot more names than companies.
They can also have a first name, middle name,
saluatation, suffix, nick name.
There are a couple things humans have that companies don't:
Gender and Date of Birth
Remember we said that if the data in a field is short
to use a short fieldname?
Our Salutation field will be short --
something like Mr. or Ms.,
so we will shorten the fieldname to 'Salut'
For now, we will refer to these as 'human fields'
Humans and companies both have a Category and Notes,
Every Table should have a way to uniquely identify each record.
A Primary Key is a unique field,
or combination of fields, in a Table.
Access has a Data Type called AutoNumber,
which is a unique Long Integer generated by Access.
This is, for the most part, what we will use for the Primary Key.
In the Categories table, CatID is the Primary Key.
In the People table, CatID is a Foreign Key.
So what is CatID in the People table?
That's right, it is a foreign key.
And if we have CatID, then we do not need the Category field.
There are two types of key field schemes.
Natural Keys are values such as Account Number
or Product Code.
Advantages are that they are meaningful,
the values already exist, and they are easy to recognize.
Artifical Keys are meaningless generated values.
Advantages are that they don’t take much space to store
so they are efficient and often faster to link,
no need for composite (multi-field) keys
or worry about changing meaningful data that
that may be part of a natural key.
Key fields can be transparent to the user
so the fact that they don't mean anything -- doesn't matter!
So, which is better? Natural or Artificial?
Like naming, everyone has their own opinion.
Some of the top experts in the business, such as John Viescas,
who has written several excellent books
is in favor of Natural Keys.
I like Artificial Keys,
except for State and Country Abbreviations,
which are only 2 characters and have standard values.
As we design tables,
we should also determine how they relate to each other.
We already know that a Category must first exist
before a Person can be assigned to it.
The most common type of relationship
is a One-to-Many relationship.
For each ONE category, such as Friend,
there can be MANY prople.
There are 3 types of Relationships.
A One to One Relationship is when one record in the main table
correlates to just one record in a related table.
A One to Many Relationship is when one record in the main table
correlates to many records in a related table.
A Many to Many Relationship is when
one record in the main table
corresponds to many records in the related table
AND one record in the related table
correlates to many records in the main table.
One to One Relationship Examples
All humans and companies are in the People table.
A person may also be an Employee, in which case,
there would be information in an Employees table.
For security purposes, you may choose to list
financial information in a separate table
to make it easier to restrict access to sensitive information.
Your Expenses table may have everything together
to make it easier to see
how much money is being spent on a project,
but the detail to justify the cost may be different
depending on what it is.
One to Many Relationship Examples
A person may have many phone numbers.
An invoice may have many detail lines.
A Music CD usually has many songs.
A Recipe has many ingredients.
Many to Many Relationship Examples
People can have many Addresses
and an Address may have many People.
A Photo can have many People
and a Person may be in many Photos
A Supplier can carry many Products
and a single Product may be available from many Suppliers.
A Student takes many Classes
and each Class has many Students.
We discussed the Leszynski/Reddick Naming Convention.
We set up fieldnames for the table that is
the heart of our database, People.
We answered Barbara's question about why addresses
and phone numbers are stored in separate tables.
Primary Keys and Foreign Keys are very important.
There are three types of relationships:
one-to-one, one-to-many, and many-to-many
In the next lesson, we will structure the rest of the inital tables,
create fieldnames,
and discuss more relationships that will be needed.
The concepts that we are covering are very important
and I want you to have successful databases!
(music by Ken Gaines)