Storing Unique Database Information

Store Unique Database Information in One Place:

Most database developers understand the basic concept of data normalisation. Ideally, you'd like to store the same data in one place and refer to it with an ID when you need to reference it. Therefore, if some information changes, you can change it in one place and the information changes throughout your application.

For instance, a customer table would store a record for each customer, including name, address, phone numbers, e-mail address, and other characteristics. The customer table would have a unique CustomerID field (usually an Autonumber field if there is no 'natural' key) that is its key field and used by other tables to refer to the customer. Therefore, an invoice table, rather than storing all the customer information with each invoice (because the same customer may have multiple invoices), would simply refer to the customer ID value, which could be used to look up the customer details in the customer table.

Access makes it very easy to do this through its powerful forms that use combo boxes and subforms. If you need to make a change to the customer's information (such as a new phone number), you can change it in the customer table and know that any other part of your application that references that information is automatically updated.

With a properly normalised database, changes to data over time are easily handled with a simple edit. Improperly normalised databases often include programming or queries to make changes across multiple records or tables. This not only requires more work to implement, but it also increases the chances of the data becoming inconsistent if the code or queries don't execute properly.

 

Null Values Defined

Defining Null Values:

A Null represents an unknown or a missing value. It is important to understand that a null value does not mean a zero of a text string of one or more blank spaces.

§  Zero (0) can have many different meanings. A zero can represent the balance of your bank account, the amount of a product in stock or the amount of art classes with spaces left on them.

§  A text string of one or more blank characters will mean absolutely nothing to most people, however it may be particularly useful to a query language such as SQL. In SQL a blank space is a valid character, as is a string of two blank spaces ('  '). They hold the same importance as a string of two letters ('XY'). In figure 1 shown below, a blank represents the fact that Warrington does not appear to be located in any County.

§  A zero-length string, two consecutive quotes & no space between (''), is also deemed an acceptable value to query languages such as SQL and can be meaningful in certain circumstances. You may have used databases containing an Employee table, and in this table along with FirstName and LastName fields you will also see a MiddleInitial field; this may often contain a zero-length string due to the fact that an Employee may not have a Middle Initial.

ClientID	FirstName	MiddleInitial	LastName	City/Town	County
001	James	M	Lister	Warrington
002	Samual		Helms	Walsall	West Midlands
003	Tracy	L	Sanders	Liverpool	Merseyside
004	Henry		Thomas	Sale
005	Tim		Lee	Scarborough	North Yorkshire
006	Elizabeth	H	Brown	Worthing	West Sussex
Figure 1: The Clients Table

The Value of Nulls

When used for its stated purpose, null can be quite useful as shown in the Clients table in figure 1. The null values seen in the ClientMiddleInitial field and the ClientCounty field represent either missing or unknown values. In order to use a null correctly, you will need to understand why they occur:

Human error is commonly associated with the resultant missing values. An example of this may be seen if we consider the record for Henry Thomas in the above table. If you are entering the data for this record and you fail to ask the client for the County name they live in the data will then be considered missing and as such the record will be shown including a null. Once this error is realised however, you can then correct it by calling the client.

Unknown values can appear for a variety of reasons. An example of this may be that the value for a field has not yet been defined. On a student course database, there may be a Categories table that defines that type of courses available; you may add a new course to your list, but as yet there may not be a correct category defined that this course will fit in to. Another example of an unknown value is that it is truly unknown. If we refer back to the example concerning Henry Thomas contained in the Clients table; when entering the data for this record, you have asked him for the County that he lives in, he may not actually know the county name. If you also do not know the name of the county in question, then at this moment in time, the value is truly unknown. You can return to correct this problem when either of you determines the correct county name.

You may also have a case for a null value if you are working in a database and none of its values applies to a record. An example of this may be the Employees database and a table that contains an HourlyRate field and a Salary field. You may see null records here as the fact is the employee won't be paid a fixed salary and an hourly rate, therefore one will always be null.

You must also consider that there is a slim difference between "is not applicable" and "does not apply". In the previous example, "does not apply" is the correct definition as the value definitely can not apply to one or the other of the fields. As another example, suppose you are working in a database that includes a Patient table, and that the table includes a field relating to Hair Colour; if you are updating this and a patient recently lost all of their hair then the field will need to include "not applicable". You could use a Null value in this field, although it is recommendable to use a true value such as "N/A" or "Not Applicable", making information much clearer to the user.

Allowing Nulls in a table will depend on exactly how you are wishing to use the data.

Below deals with the negative elements of using Nulls within your database:

The Problem with Nulls

Including null values within your data can have an adverse effect when using this data within any mathematical operations. Any operation that includes a null value will result in a null; this being logical as if a value is unknown then the result of the operation will also be unknown. Below shows how using a null in a calculation will alter the outcome:

(100 x 3) + 10 = 310

(Null x 4) + 10 = Null

(20 x Null) + 100 = Null

(20 x 100) + Null = Null

Using the products table below, you can see the effects that including Nulls will have on any mathematical expressions you have used:

ProductCode	ProductName	CategoryDescription	Price	QtyInStock	StockValue
001	Word 2002 Bible	Books		10
002	Access 2002 Bible	Books	29.99	5	149.95
003	28" TFT JVC Television		995.00
004	Nokia 310 Mobile Phone		65.00	10	650.00
005	14" Sanyo Portable TV	Audio/Visual	149.99
006	128mb Pen Drive		49.00	15	735.00
Products

Notice how including nulls in a table will effect mathematical operations

You can see an example of the problem that null values cause when looking at certain records in this table. In this table, the StockValue field derives its results by using the Price and the QuantityInStock values, i.e. [Price]*[QtyInStock]. You can see that for the first record, ProductCode 001, that where a user has not entered a value for the Price field (i.e. a Null) then the StockValue also shows as Null.

This can lead to more severe problems as this may go undetected. Suppose you need to calculate the value of all items that you currently have In Stock; you will not receive an accurate result as values will not be taken into account.

To ensure that the Stock Value can always be calculated, you must first ensure that the Price and the QtyInStock fields can never contain a NULL value.

A further example of the effects of null values can be seen below:

Null values will also effect aggregate functions that incorporate the values of a given field. If we were using an aggregate function, for example a Count function, the result will always contain a null if the field contains a null. The table below shows the results of performing a summary calculation that will count the number of occurrences of a category in the Products table.

CategoryDescription	Total No. Of Occurrences
	0
Audio/Visual	2
Books	2
Category Summary

Null values will effect the results from an aggregate function

The Total No. Of Occurrences is the result of using the expression Count([CategoryDescription])

The table shows that there are "0" occurrences of the unspecified category, implying that each Product has been assigned to a category. This is clearly not the case, as viewing the Products table will show that there are two products that do not belong in any category at present.

Using all of this information, you should now be able to see that allowing missing or unknown values will have adverse effects on other things that you may wish to do with your data and should be considered when in the process of database design basic steps.

 

Student/Course Logging Database

Student/Course Logging Example

 

Submitted by : Edgar Mata
Created: 2001 - Version 1.0
Tools Used: Access 2000 and Macromedia Fireworks (Images)
Client/Market for App: IT Training Company

Database Login Form: This screen shows the initial database login, and uses the combo box to allow the user to select their login name from the list.

Database Loading Screen: Once logged in the database loading screen, including a welcome message is displayed.

Database Switchboard: After loading of the database, you are taken to the main switchboard form, created using a blank form and not the switchboard manager.

Here the user is presented with the various options available to them.

Data Entry Form: Here the user enters details relating to the Sales Advisors who deal with the selling of the courses. The tabbed form allows entry of their specific details.

From here the user can add new student details relating to the course advisor and can view any students already signed up by the advisor in question.

Reports can be generated from here and searches made.

Search Form: The search form allows for searches to be made relative to Students/Advisors/Courses. The combo box is populated with information relevant to the search option selected within the option group.

When a choice is made in the combo box, the search facility is closed and the associated record displayed.

Reports Form: The report screen allows the selection of various reports available to the user.

The first four options allow reports to be generated relevant to each subject.

Selecting "Student Course Details" will populate the list box (which is not visible until this option is selected), and then allows the user to filter the report to only display the selected record for preview.

User Interface Design Tips

Microsoft Access Graphical User Interface Design Tips

Many people rush into designing the User Interface for their Access database before thinking about layouts, formats and usability. The following tips from David McQueen may greatly assist when considering all of the options available:

This is my primary role in designing Access applications, and having been drafted in plenty of times to "have a look" at some of the forms and reports that have been designed by others, I think that I am able to hand a bit of advice to others.

1.     Users love forms designed in Tahoma 8. I know this old hat to many developers but I am amazed by the amount of screens I come across with a combo of Times New Roman and Bauhaus.

2.     Design reports with Title, Date, UserName and Printed Time in the Header preferably.

3.     Think of the dumbest user ( I know that sounds insulting, but...) possible and ensure that even they can easily read what is on the screen.

4.     Ensure that the design pattern is consistent across the software. For example if you double click to open an item on one list ensure that you can do the same on another form.

5.     Use colour sparingly. Stick to the gray/blue backgrounds and limit the extensive use of colour. If there are items such as required fields on a form, use one background colour for all of these fields, e.g. yellow

6.     Ensure a consistent easy navigation between screens/modules. I prefer to have a consistent top toolbar wherever the user is in the software, or if users don't like that to have a consistent sidebar/treeview.

7.     Type error trapping/required input fields and match them to a dialog box. Nothing worse than a user seeing "Error 76a..blah, blah" when a standard procedure has gone belly up.

8.     Keep screens as uncluttered as possible and please, please, please.....ALIGN the controls on the form.

9.     For objects that are not applicable to a certain user or process, just gray them out/disable them, rather than deleting them. It helps especially when users have made a mental image of what the screen should look like.

10.   Ensure that data in fields is justified appropriately. Right for numbers (integers) and Left for strings.

Thanks to David for the above tips.

To see some examples of the designs being used for a User Interface please go to the GUI Design Examples Label.

Objectives of Good Database Design

Objectives of Good Relational Database Design:

There are many distinct objectives that you must achieve in order to design a good, sound, structured database. You can avoid many of the problems you may encounter by keeping the following objectives in mind and constantly focus on these whilst designing your database.

§  The database supports both required and ad hoc (unplanned) information retrieval. The database must be designed to store the data necessary to support information requirements defined during the design process and any possible ad hoc queries that may be posed by the users.

§  The tables are constructed properly and efficiently. Each table in the database must represent a single subject only and should be composed of relatively distinct fields which keep redundant data to an absolute minimum and should be identified throughout the database by a field with unique values.

§  Data integrity is imposed at the field, table and relationship levels. These levels of integrity help guarantee that the data structures and their values will be valid and as accurate as possible at all times.

§  The database should support business rules relevant to the organization it is designed for.The data must provide accurate information that is always meaningful to the business.

§  The database should lend itself to future growth and development. The database structure should be easily modifiable and expendable as the information requirements of the business continue to change and grow.

You may find it difficult at times to fulfill all of these objectives, but you'll certainly be pleased with your final database design structure once you've met them.

Benefits of Good Database Design:

The time that you invest in designing a sound, well structured database is time well spent. Good database design saves you time in the long run because you do not have to spend time constantly revamping a quickly and poorly designed structure. You gain the following benefits when you apply good design techniques:

§  The database structure is easy to modify and maintain. Modifications you make to a table or field will not adversely affect other tables or fields in the database.

§  The data is easy to modify. Changes that you make to the value of a given field will not adversely affect the values of other fields within the table. Furthermore, a well-designed database keeps duplicate fields to an absolute minimum, so you typically modify a particular data value in one field only.

§  Information is easy to retrieve. You should be able to create queries easily due to well constructed tables and the relationships between them are correctly established.

§  End-User applications are easy to develop and build. You can spend more time on programming and addressing the data manipulation tasks at hand, instead of working around the inevitable problems that will arise when you work with a poorly designed database.