Development of the Design

Let’s begin the design phase by revisiting two terms defined in Chapter 1: database and database application. A database is a structure where data is kept. The structure has tools for entering, editing, and retrieving the data. As you will see shortly, it is important that you build this environment properly.

A database application contains the programming that will interact with the database. It should automate the ability to:

• Enter and edit data

• Retrieve data with the use of queries

• Retrieve data with the use of reports

A database application could be a combination of several small applications. Many times, modular construction (better known as “divide and conquer”) will help make the whole project more manageable and will facilitate the meeting of conflicting needs.

One team may be developing a database application for the order processing department, while another team may be developing a database application for inventory control. It may be the job of a third team to coordinate and connect the work of the first two teams.

As I stated earlier, the work begins with the algorithm, or steps needed to complete the process. However, you now need to translate this pseudocode into a database and VBA code. Many textbooks on the subject of database design use modeling systems that allow developers to translate the steps into a database application. We will look at the most popular of the models, the entity-relationship model.

Entity-Relationship Model

Sometimes called the object-model relationship, the entity-relationship (E-R) model defines four elements and, in many ways, defines the basis for object-oriented programming. The four elements are

• Entity, or object

• Attribute

• Identity

• Relationship

Entity, or Object

These are the nouns of the project. For instance, if you design a database application for a bookstore, CUSTOMER will be one entity, BOOKS another, AUTHORS a third, and so forth. Each entity is called a class. Notice that I didn’t say that each customer would be an entity, only an entity of type CUSTOMER. That is an important distinction that encompasses all of the customers.

As I have said many times already, it is important to design your database application on paper. At this phase, you should go through the design and pick out the nouns. Each noun will be a class.

Attribute

Class CUSTOMER will have certain characteristics or properties: name, gender, age, reading preferences, and so on. We call these the attributes of the class (in this case the class of customers).

The actual attributes may change from customer to customer. One customer may prefer mysteries, while another may prefer biographies. However, we can agree that all the members of class CUSTOMER will have a reading preference.

Identity

I don’t think it would come as a great surprise that each member of class CUSTOMER has a unique identity. That identity could be a name, or a customer number of some sort. How else could we identify each entity, or object, within the class CUSTOMER?

Relationship

All these objects, or classes of entities, do not live in an isolated world. Many times, a relationship needs to be established between them. The three relationships are one-to-one, one-to-many, and many-to-many.

As an example of a one-to-one relationship, let’s return to the class CUSTOMER. Now let’s define another class called STORE (assuming this is a chain, and they are spread over a wide geographic location). Each CUSTOMER shops in one STORE.

The one-to-many relationship is the most common. As an example, class CUSTOMER will have many of class BOOKS.

The many-to-many relationship is the rarest and most difficult to handle. As an example, there may be cases in which AUTHOR A and AUTHOR B collaborated for BOOK A and BOOK B. This takes a bit of design work, which we will address in Chapter 3.

The E-R model gives us a working framework to construct our design and eventually translate it into code.

Translating the E-R Model into an Access Database

All database programs adhere to the relational database model developed by IBM in 1970. This allows us to give each class its own table (a table is the database component that holds data) and then relate the tables together.

As an example, you could create a table called Customer, as shown in Figure 2-1. The noun is CUSTOMER. Class CUSTOMER has attributes such as a customer number, last name, first name, and so on. The identifier for each customer is the customer number. Since it is the unique identifier, I made that a key field to keep it unique.

Figure 2-1: The Customer table in Design View

You could set up a second table to track the inventory, as shown in Figure 2-2. Noun INVENTORY has the attributes of ISBN number, title, author number, and price. The unique identifier is the ISBN number.

Figure 2-2: The Inventory table in Design View

Finally, you could have a table to track a customer’s purchase. It might look like the one shown in Figure 2-3.

Figure 2-3: The Purchases table in Design View

Here we have a purchase number, customer number, ISBN number, purchase date, and price. Notice that this table employs the customer number from one table and the ISBN number from another table. You can see a graphic representation of these relationships in Figure 2-4.

Figure 2-4: The Relationships window

This is a simple scenario, but it illustrates entities (or objects), with attributes, identities, and relationships. We took those qualities and translated them to tables with fields in Access and then related them with unique fields.

The Purchases table serves as a joining point for the Customer and Inventory tables. Since customers usually make multiple purchases, the relationship between the Customer and Purchases tables is a one-to-many relationship. The Inventory table would not usually have a separate listing for each book of the same ISBN number. Instead it would probably have an attribute called quantity. (Note that a quantity attribute is not shown in Figure 2-4. We will be modifying this table as we progress through the book.) Since there will probably be multiple purchases of the same book, this will be a one-to-many relationship also.

Note

The example database used here can be downloaded from www.osborne.com.

The process of properly distributing the data among specialized tables is called normalizing the data.

As you can see in Figures 2-1, 2-2, and 2-3, Access provides a place to document each field in the table’s Design View. It is a good idea to print out the structure of each table, as well as the Relationships window, so that they can be easily referenced by each member of the team as well as the client.

Admittedly, all of this is a simplification geared for the beginner. There will be times when the relationships will not be as clear and direct. There may also be situations when the client will have unique requirements that call for unusual table designs. However, the scenario given here is more the rule than the exception. Good design will usually have the flexibility to meet any demand. Bad design will greatly limit your choices.

Once the database itself is designed, you employ VBA to help complete tasks that the database cannot do on its own. You need to make decisions as to what the database can do on its own and what will need help from VBA. You may want the code to automatically order more copies of a book when the quantity drops below a certain number. Or you may need it to automatically trigger certain reports.

With Access’s ability to easily build reports, forms, relationships, and queries, you can accomplish a lot without programming. Needless to say, this will help you complete the project quickly with a minimum of bugs.

As you will see as we progress through the book, your designs must take into account errors that could prevent the normal flow of the process. You should try to anticipate what errors can occur and, if they do, how to handle them.

Another factor in design is security. Security encompasses not only malicious attacks by outside hackers, but also who has authorization to see or edit what information.

During this design phase, you must be careful of “feature creep.” It is not unusual for a client to say, “If it can do this, can we get it to do that now?” Again, good documentation and client signatures can prevent a lot of this from happening.

We will be spending the rest of this book just discussing design issues. Assuming this phase is finished, you are now ready for implementation and subsequent testing.