[ad_1]
Microsoft Access Database Design Concept – Step 5 of 7: Table Field Design of the Access Database
The Field Design & Validations
This is the last step which completes the ‘back-end’ database design process and steps 6 and 7 (later articles to follow) focuses on the ‘front-end’ database design process and is deemed optional to this final step of the ‘back-end’ database design process. In this section, I’m going to discuss the process which talks about the field’s properties and the business rules of how fields control data input.
If you are not designing input screens (Access Forms), then the process could effectively stop here. If however, you intend to handle ‘front-end’ processes for your database, this is the preliminary step of setting the best fit attributes (ideally in table design view mode).
You will therefore need to know about what properties are and how and where you apply them. Properties is a general term to describe an attribute of an object. For an example, a field (the object) has many attributes to it (the properties). So take a field called ‘Post Code’ would typically have the following properties:
Property Value
Name Post Code
Data Type Text
Field Size 10
Indexed Yes, Duplicates OK
Required Yes
Format > (meaning upper case)
There are many other properties but your should now understand the concept and terminology of a property to the object.
Some of the properties are deemed mandatory and obvious like the ‘Name’ and ‘Data Type’ where others are optional and change the behaviour or sensitivity of how a field is controlled. The key element here from the database design process is to set as much as you can at the table design time which sets standard for other dependent objects that follow (namely Queries, Forms and Reports).
A couple of properties that may be considered and constitute as a business rule is the Validation Rule and Validation Text.
Two examples:
A) An Order Date where by the rule would enforce an order date for a new order to always be the present or future date but never a date in the past.
Validation Rule Property would look like >=Date() where the ‘Date’ is a function defaulting the current date. If the rule is violated, then it is rejected and cannot be stored.
Validation Text Property is a free text narrative that is displayed in a message box when the rule is violated.
B) The Quantity field where by any new item to a new order must always be a value greater than 0 (zero) and can therefore not store a 0 (zero) or negative numbers.
Validation Rule Property would look like >0 where the logical operator enforces only a positive number starting at Again, adding the Validation Text Property to it makes this a more user friendlier and easy way to prompt the end-user.
There are many properties and you will need to research each one and set as many as you think suitable.
List all rules that can be identified by revisiting all tables and fields from the previous reverse engineering tasks and then you will be ready to move onto the ‘front-end’ steps that follow.
[ad_2]
Source by Ben S Beitler
