Understanding Data Types
Creating an effective test harness for a relational database must involve the population of meaningful data that can contest your input procedures and outputs (queries, reports and exports). As such ‘good data’ means populating your database with acceptable field conditions to avoid potential data issues by choosing the correct data types and field properties.
Text – Fields that contain letters, numeric characters, spaces and various other symbols. In use the text data type is so flexible and open to any possible input that it is probably best to specify a fixed length.
Memo – Memo fields can contain the same items as text fields and are variable in length. Able to contain up to 65,535 characters of data.
Number – Fields that store ‘meaningful’ numbers and enable you to perform mathematical operations on the data stored within them. They can be 1, 2, 4 or 8 bytes in size.
Date/Time – Date/time fields store dates and times in various formats. For example Long Date: Wednesday, 30 July, 2008, Medium Date: 30-jul-08, Short Date: 30/7/08. Again, calculations can be performed on the date/time field.
Currency – Currency or monetary values stored in a similar way to numeric data fields but obviously such numbers are preceded by a currency symbol. Here negative values appear in brackets and you can configure currency fields to automatically round to a specific number of decimal places.
Autonumber – Is a numeric value field which automatically increments each record as you add more to the end of a table. This is a commonly used data type for primary keys as it enforces quality data control of cell items.
Yes\No – Yes/No, True/False, On/Off data types which require input of one option from two choices. As a result null values are not allowed.
OLE – Fields that contain objects that you place in the table from related programs that support Object Linking and Embedding. They are very powerful and can seriously enhance a databases aesthetics. Such fields can store up to 1GB of data which makes embedding multimedia files and large offshoot databases highly practical.
Hyperlink – The hyperlink field simply is a holding place to store a hyperlink address (URL) for quick launching to the Internet.
Lookup Wizard – Although not a traditional data type the Lookup Wizard allows users to create fields for lookup purposes in other tables. These ‘masterfiles’ can then be accessed across the board by any tables as drop-down options to choose from.
A few pointers when choosing the relevant data type
- When storing money and numeric values which require a high degree of accuracy choose Currency.
- When storing text up to 255 characters or numbers that will not be used in calculations choose Text.
- When storing numbers that may require calculation choose Currency or Number.
- When storing large amounts of text, i.e. over 255 characters, choose Memo.
- When storing dates and times, its self explanatory, choose Date/Time.
- When storing whole numbers of considerable length choose Number with Integer in the fieldsize property.
- When storing long whole numbers choose Number with Long Integer in the fieldsize property.
- When storing single precision numbers that include decimal places choose Number with Single in the fieldsize property.
- When storing double precision numbers that may have decimal places choose Number with Double in the fieldsize property.
- When storing unique, random numbers choose AutoNumber with Random in the NewValues property.
- When storing unique, incremented ID numbers such as key fields choose AutoNumber with Increment in the NewValues property.
- When storing globally unique identifiers such as those used within replicated databases choose AutoNumber with Replication ID as the field size.
In the following part we will discuss the importance of Field Types and how they can be used to shape the effectiveness of your databases.
This post is part of the series: Designing a Database with Microsoft Access
- Microsoft Access: Designing a Relational Database (Part 1)
- Microsoft Access: Understanding Data Types
- Microsoft Access: Understanding Field Properties
- Microsoft Access: Understanding Lookup Fields
- Microsoft Access: Join Properties and Join Types
- Microsoft Access: Defining Relationship Types
- Microsoft Access: Setting Up Relationships Between Tables
- Microsoft Access: Designing a Relational Database (Part 2)