Hi, this is Crystal. { Click HQ to watch in High Quality }
Planning a database means that you need to
SEE what you are going to keep track of
so that you can describe it in a structured way.
When you are planning,
instead of focusing on what you want out,
focus on what you have to put in.
A good design is everything when it comes to Access.
It takes time to plan a good database.
Structuring data is the most important thing to do well.
So where do you start?
Prepare -- clear a place to work,
make a Refreshing drink,
get Paper & Pencils (and notes), play Inspiring Music.
Gather Examples of what needs to be organized
… and think about what you have to build with.
("I Don't Know What I Want" by Jubal Lee Young)
Clear your mind and visualize.
See the different "nouns" that you will keep track of.
Nouns are Tables.
What will you keep track of for each of your tables?
List items under each table.
Visualize what you write. What is it?
What are examples of its values? See it in your head.
If it is a noun or event, then it is a table.
If it is a property that describes something,
like an adjective describes a noun, then it is a field.
The important thing now is to list everything you can think of,
down to the smallest detail.
If items are in the wrong place, we will refine it later.
One of the properties that describes a person is a name.
A name has many parts – break them down.
Store information distinctly.
When you look at a name, see each part.
Another property of a person is Gender: Male or Female.
Birthday does not describe a person,
it is a property of an event,
but since a person has just one birthday in their lifetime,
it will help us identify who a person is.
There are several ways we can categorize people.
This also helps us find them easier,
like the yellow pages of a telephone book .
Friend, Colleague, Family, Mailman,
Paperboy, Grocer, Handyman, Artist, Musician, Teacher, …
We could have several Notes and some of these
may result in things that need to be done.
Make a Table with each Category
in a separate row, as a separate record.
This way, categories can be added as needed
and we don't have to think of everything now.
Before people can be categorized,
the category must first exist.
Since we read from left to right,
move the Category table to the left of the People table .
visualize
It doesn't matter what role someone plays
when it comes to data, we are all the same.
We have a table for People …
what about Companies?
Do we need a different table keep track of Companies?
Good question …
What is a Company?
A Company is a collection of People.
What do Companies have in common with Humans?
Name? Address? Phone Number? Email? Website?
So people and groups of people have a lot in common.
Can the same structure be used
to keep track of Human and Company information?
Visualize different types of People
...by themselves ... or as a collection
… the data is the same.
The next step is to turn items into Field Names.
A Field is a column of information in a table.
A row is a Record of information in a table.
Make sure the names you choose to call fields
are not reserved for another use in Access.
Look up Problem Names and Reserved Words
on Allen Browne's site.
Go to www.AllenBrowne.com
Click on Microsoft Access tips.
Look in the Application/Utilities section
at Utilities
then click on ''Problem names and reserved words"
You can also Google ‘Allen Browne’
... and even spell his name wrong …
35 million hits,
but what you want is right here:
‘Access Tips’
When Allen’s page comes up,
press CTRL-F to launch the Find box
in the lower left corner of the screen.
Type ‘Reserved’ and press ENTER.
You will find this to be such a useful page that you may
want to add it to your bookmarks toolbar.
Let us see if the word "Name" is okay to use as a fieldname.
It starts with 'N', so click 'N' in the alphabet list.
Name is reserved for Forms, Reports and other objects,
so we should not use it.
‘New’ is a future SQL Server keyword.
‘Next’ is a Jet reserved word .
Jet is a term that means the engine that Access uses.
‘Next’ represents an action, so it is a Method.
‘No’ is reserved for Jet.
If you scroll to the bottom of the list,
you will wind up at X, Y, and Z.
There are over 2500 words on this list.
Don't worry, you will get know the words
that you might be tempted to use.
As you learn more about Access, you will recognize
many words without having to look them up.
Only use letters, numbers, and underscores.
Don’t use spaces, special characters, or extra words.
Start names with a letter, not a number.
If you start a fieldname with a number, you
WILL have problems and you will probably
pull your hair out trying to find out what is wrong.
Don’t use spaces.
Don’t start with a number.
A photo is a separate thing than a Person.
Store information about Photos in a related table.
Keep names concise yet descriptive.
If you have a database that includes Products,
‘Prod’ is easily recognized as an abbreviation for Product.
‘ID’ should be qualified with what kind of an ID it is.
By itself,
ID is an ambiguous name
Think about the length of your data;
if it is not many characters, then use a short fieldname.
Fieldnames do not wrap in a Datasheet view
… or in a relationship diagram.
State Abbreviation is too long,
the data will only be 2 characters.
'State' would be okay except it is a reserved word
so just use 'St'.
Another reason to keep names short is
less to type when you are programming.
Yes, you also going to learn, or get better with
Visual Basic for Applications
It is easy! Stick with me and I will show you how.
Name your fields generically so the names are flexible.
Don’t use someone's name as a fieldname.
If you are keeping track of who an account belongs to,
store Employees in a separate table
and link on EmployeeID.
What happens if you hire more people?
You would have to add more fields,
and it would also be a lot harder to report your data.
What did we learn about Planning a Database?
Prepare
Visualize
Organize
Design for Flexibility
Use Guidelines when you are thinking of Names.
...and, did I mention Visualize?
Focus on the data, not who it belongs to.
What's Next?
More Planning. We will take the design stage further
to Create Fieldnames,
Learn what Key Fields are,
and Discuss Relationships.
("I Don't Know What I Want" by Jubal Lee Young)