Hi Curtis,
Normalizing Data
* storing data efficiently
|--- ...When you are planning your database... ---|
Instead of thinking about what you want out ...
....think about what you have to put in and structure it well.
Drawing an analogy to a building:
Data structure is the foundation.
Forms are walls.
Reports are paint.
Organize your data so the structures are strong. You want what you
build on that foundaton to be stable and flexible.
The best solution is simple... but it is the hardest to find.
It takes thought and foresight to design your structures well. And the
simplest solution is rarely easy to see. Get the structure right before
building anything else. If you have already built forms, queries, or
other objects, fix the structure before building any more.
Just as you wouldn't build a house on sand without a foundation, your
data structure is the foundation you will build on for Access.
It takes time to plan your database.
Here are some ideas for you:
Think of the different "nouns" that you will track:
Customers
Addresses
Cities
Phones
Notes
Products
Purchases
Payments
Campaigns
For each "noun", you describe it with "adjectives", or fields. Each
table should have a primary key. It is common to use the autonumber
field and "ID", such as CustomerID, , ProductID.
By convention, when "ID" is used in a fieldname, it indicates that field
was originally created by an autonumber and it is a LONG INTEGER data type.
Do not use "ID" as a fieldname as it is ambiguous and not descriptive.
Don't repeat data structures
If you see that you have address information in more than one table,
create an Addresses table and use an autonumber AddressID to relate the
data.
Key Fields
Key fields are used to link related tables together and long integers
are commonly used. When you create an autonumber field in access in one
table, create a long integer field in another table to match up to it.
The field names in the different tables should be the same when they
represent the same piece of information.
DefaultValue
Change default value of all numeric foreign key fields to Null --
Microsoft default is 0, which will never match with an autonumber field
-- not changing it will prevent you from being able to enforce
referential integrity if it is not specified. To hammer that in...
Access sets the DefaultValue of numeric fields to 0 -- this is not good
for a foreign key because there won't be a key field to match it to : It
is okay if it is not filled out, but it is NOT okay if it is filled out
with a value that doesn't match.
Indexes
You can also create indexes on tables. For instance, you may have a
combination of fields that should be unique, such as TestID and
QuestionID, where you would not want the same question to appear on a
test more than once. You can create a unique index on the combination.
Think of which fields you will be doing lookups on and build indexes,
but do this judiciously as indexes are updated when records are changed,
so they take extra time to maintain, and there is a limit to how many
you can create.
Generally, you will have several Lookup tables. For instance, Products
would be a lookup table when you want to collect a ProductID in another
table, such as Purchases.
Do not store names, store the IDs. For instance, do not store "Sue
Smith", but use the PID (PersonID) that identifies her (* see Combobox
Example on how to do this).
Lookup Fields in the table design
Another thing Microsoft allows you do is define comboboxes as part of
the table design. I do not recommend this. Data should be entered via
forms, which is where you would set up comboboxes.
Data Type for Key Fields
Don't use ANYTHING but text or integer, or long integer for key fields
-- double-precision numbers are not accurate for keys. If you do use a
text field for a key, keep in mind that it needs to be short to be
efficient. It takes about 1 byte to store each character whereas long
integers only require 4 bytes to store. Normally, long integers are used
for key fields.
Names
DON'T use anything but letters, numbers, and underscores in fieldnames
and tablenames.
Don't use special characters in names (%, &, /, etc). Personally, I
don't even use spaces. Start all names with a letter not a number.
Using numbers usually indicates that the data is not normalized anyway.
If you start a fieldname with a number, you WILL have problems, so don't
ever do it. Microsoft allows you to do many things that kick you in the
butt later.
Think about how long text fields will be and set the Field size to
something other than the default of 50 characters. For instance, 30 is
usually long enough for cities, 10 long enough for zips, 14 for phone
numbers, 15 or 20 for last or first name (I let firstname be longer in
case there are 2 of them).
Keep names concise yet descriptive.
"Date" is a bad name for a field since that is a reserved word. Qualify
fieldnames, like CourseDate and SaleDate. One reason to keep fieldnames
short is consideration of the length of the field that the column
heading will be over since column headings can't wrap when you open a
table. Another is less to type when you are coding.
Name your fields and tables well. When you do create forms and reports,
name the controls to something logical before building any code. Names
like Text34 and Combo68 make it frustrating to code and even more so if
you need help and others have to decipher what those names really mean.
Personally, I like to make the Name property of the control the same
as the ControlSource property whenever possible.
When you have command buttons, name them cmdClose, cmdOpenReportMenu, etc.
***
some more considerations:
Although convention tells us to prefix tables with "tbl", I don't like
to do that. Takes a split second longer to scan for values. If you do
want to group your tables, use something short, like T_
Which is easier to read?
tblCustomers
t_ Customers
Customers
If you are going to use a long table prefix such as "tblABC" ...make it
quicker to read the important part -- the actual table name.
Which can you read faster ...
tblABCLocation
tblABCLevel
or
tblABC_Location
tblABC_Level
always keep Names concise yet descriptive
Numbers that aren’t numbers
Unless you plan to do math, set "numbers" to text data type. Especially
if they have symbols such as "(", "-" such as phone numbers. It is more
efficient, however, to store numbers as long integers than text because
the number of bytes of storage is less. Personally, I give that up for
keeping the symbols.
InputMask
When you use the InputMask property on a text field, choose to store
symbols in the field so when you do an export, they will be there. For
instance, without storing symbols, a phone number would be 1234567890
when exported as opposed to 123-456-7890 or (123) 456-7890.
Allow Zero Length
Make sure Allow Zero Length is set to Yes for text fields if you are
planning on importing or using APPEND queries to add data and fields may
be empty strings.
Captions
Don't use captions for your fieldnames in your table designs.
Personally, I think this is a bad idea. Users should never enter
information directly into a table. Therefore, the only people that
should be opening the table directly are administrators and using
captions hides the real field name.
If you want to call "ItemID" something else like TaskID, then NAME it that.
Descriptions
Fill out your field Descriptions! This is what the status bar text will
be set to when you slide that field onto a form. The StatusBar text
shows up in the lower left corner of the screen on the StatusBar when
you are in that field.
Tracking date record was created or updated
Add these 2 fields to all your tables (except lookups) and make them the
last 2 fields.
DateCreate, date, DefaultValue = Now()
DateUpdate, date – set on the form BeforeUpdate event
the best way to use the DateCreate field is to set a default value of
=Now()
in the table design.
For DateUpdate, make sure it is on your form (I put it in the form
footer and LOCK it. Then, use the Form BeforeUpdate event to set the value
me. DateUpdate = now()
Combobox Example
* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…
Create an autonumber field in the People table -->
PID, autonumber
then, in the other tables...
PID, long, DefaultValue = Null
Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.
create a combobox control
Name --> PID
ControlSource --> PID
RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
BoundColumn --> 1
ColumnCount --> 3
columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)
ListWidth --> 3
(should add up to the sum of the column widths)
if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.
PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.
If you want to show other information from your combobox in other
controls, you can use calculated fields.
For instance
textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)
The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access
'~~~~~~~~~~~~~~~~~~~~~~~~
"You mean I would have to get the autonumber manually for each ..."
no, everything would be done in your code
'~~~~~~~~~~~~~~~~~~~~~
"But why have so many tables for, what are the reasons you say that for? "
You need to look down the road, Curtis. One of the reasons for using
Access to track your data is so that you can take advantage of its
incredible power -- as you learn more about Access, you will be glad if
you take the time now to structure your data well because you will have
more flexibility to do what you want.
Once you have historical data in your tables, you can see what your
database can tell YOU that perhaps you never thought of...
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
