In The Data Trenches - Part 3 - Table Design

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.