Microsoft Access Database Design - Step 3 Of 7: Data Design Field For Your Microsoft Access Database

From your reports, break down into natural units that will be the foundation for your Access database design. In simplified terms, an orders database system might consist of many tables but a core process like an order transaction could be made up from five tables (as described further in this article).

The Data Design (your fields) should start to get a little easier to complete this task providing you have spent enough time on the previous two steps of this series.

This step handles each table that has been sketched out, a list of fields that will be defined and their best matching and qualifying data types.

For example, Lets say you have determined that there are five tables for an example 'Order' document which are:

1. Customers
2. Orders
3. Order Details
4. Products
5. Shippers

Take each table and expand on this to list all the fields making sure the first field will act as the unique value and ID for a record. So the 'Customers' table first listed field might be called 'Customer ID'.

You need to capture all the fields that you believe will make up the profile of a customer across all reports bearing in mind not all fields will be required for all reports.

As part of the database normalisation theory, consider fields to handle the smallest element of data that you would normally expect to use. For example, the address of a company would not be stored in one field called 'Address' but instead across five fields ('Address Line 1', 'Address Line 2', 'City', 'Postcode' and 'Country'. This will help in grouping, sorting, filtering and calculating records at a later point.

For each field listed, decide on the best suited data type as it will drive the use of that field when reporting on it later on. So for example, will the 'Quantity' field need to be a number? Do you intend to calculate with it at some point?

A field may appear as numeric like the 'Tel No.' field but would probably be stored as the default 'Text' data type unless of course you intend to calculate with it!

Choosing the best matched data type will also start to validate the value you can enter. So you know a 'Number' data type cannot accept a non-numeric character. The 'Date/Time' data type validates only date and time (including leap years too!).

So, an example of the Orders table may have a list of the following fields and data types with sizes where applicable:

Field: Order ID, Data Type: Number (Long Integer)

Field: Customer ID, Data Type: Text (5)

Field: Shipper ID, Data Type: Number (Long Integer)

Field: Order Date, Data Type: Date/Time

Field: Required Date, Data Type: Date/Time

Field: Shipped Date, Data Type: Date/Time

Field: Order Amount, Data Type: Currency

Field: Freight Amount, Data Type: Currency

Field: Despatched, Data Type: Yes/No

Field: Invoiced, Data Type: Date/Time

The above table gives you the idea and other fields would probably come to light during this step analysis process.

Repeat this for each table and double-check that all fields have been covered against each report.
access point vs router