A database table resembles a spreadsheet – data arranged in
rows and columns. However, a spreadsheet
is intended to process integers, to sort numbers and perform complex
calculations. In contrast, a database
program is designed to organize more disparate types of information, such as
dates and text. The data types available
in MS Access 2013 are:
Short Text
Number
Currency
Auto Number
Date and
Time
Yes/No
OLE Object
Lookup and
Relationship
Rich Text
Long Text
Attachment
Hyperlink
Calculated Field
(As previously
mentioned, Access is used as our exemplar because of its wide availability.)
Each field in a table is assigned one of these types, and
all information entered into that field must of that type. For example, entering a hyperlink into a Currency
field would produce an error message.
We can apply these fields to the “Status” table discussed in
our earlier posting on normalization:
Field Name Field
Type
Project Lookup
and Relationship
Current
Status Long
Text
Status Date Date
and Time
Next Steps Long
Text
Waiting For Long
Text
Notes Long
Text
The Long Text fields would present fill-in-the blank cells,
while the Date field presents a calendar dialog box. Lookup and Relationship allows the database
designer to create either a drop down list of standard terms to be used in this
field (“Lookup”) or to tie this table to another one in the database (“Relationship”). In our example, this table would be tied
(“related”) to the main “Projects” table, and the names of all projects would
appear here as a drop down list. The
user would merely need to click “Project 1,” “Project 101,” or any other name
on the list. A look up list helps ensure
accurate and uniform data entry. If
“Agmt” is the specified description, users must use it, and may not substitute
their own terms, such as “contract” or “agreement.” While these may be accurate, they complicate attempts
to search the database.
While it is
helpful to describe each data type consistently and uniformly, each project
needs a unique name or label. Presume the database is intended to organize a
company’s contract files. Individual documents
might be described as “agmt,” “corr” or “draft” without causing confusion. But simply labeling each supply agreement as “supply
agmt” could result in confusion. Which
agreement corresponds to which vendor? A
descriptive, and unique name is called for, such as “supply agreement – ABC Co –
2014”.
Which brings
us to a fault in the table described above.
It does not ensure that each entry will contain at least one unique
entry. The standard solution is to
designate a “key” field, which must be unique, meaning that no two records in
the entire database will be identical. To
ensure that the “key” is truly unique, one might specify that it will be an “Auto
Number” field, directing the database program to automatically number each
record as it is created. The updated
table design might appear as follows:
Field Name Field
Type
ID AutoNumber
Project Lookup
and Relationship
Current
Status Long
Text
Status Date Date
and Time
Next Steps Long
Text
Waiting For Long
Text
Notes Long
Text
Once the design is finalized – and the requisite
relationships created - one may begin to enter information directly into the
table, much as one would with a spreadsheet.
Or one may elect to create an entry form to speed up the process.