Working Without VBA


Throughout this book, we will be working on a simple database called TheCornerBookstore. You can download it from www.osborne.com. I purposely kept it very simple so that the concepts would be easy to understand. In addition, we will be building on it as we go along.

At this stage, the little database does only three things:

  • Keeps track of inventory

  • Keeps track of customers

  • Keeps track of customers’ purchases

Of course, you don’t particularly need a lot of programming code to accomplish that. However, we will be doing some development to increase its capabilities.

First let’s look at our three tables. Figure 3-2 shows the structure of tblCustomer. Here the entity is the customer, and the attributes are listed in the table. We gave the customer a number for a unique identity and marked that identity by making that field a key field.

click to expand
Figure 3-2: Structure of the Customer table

Not all tables have to have a key field (sometimes called a primary key), but it is a good idea to assign one. By doing that, you give the field three characteristics:

  • The value in the field cannot be duplicated.

  • The field must contain a value in order to save the record.

  • The table will be sorted on that field.

Figures 3-3 and 3-4 show the structure of the Inventory table and Purchases table.

click to expand
Figure 3-3: Structure of the Inventory table

click to expand
Figure 3-4: Structure of the Purchases table

Relationships

As you learned in Chapter 2, there are three basic types of relationships in a database: one-to-one, one-to-many, and many-to-many. We are going to dive into them with a bit more detail.

One-to-One

In a one-to-one relationship, an entity of one type can have only one entity relationship with another type. As an example, suppose that to facilitate your mail-order customers, you have a table containing their customer number, credit card number, and expiration date. Obviously, multiple customers would not hold the same credit card number. (A customer could have multiple credit cards, but, for our purposes, we will assume they use one credit card to pay for their books.) Since one instance of CUSTOMER holds only one instance of CREDIT CARD, this is a one-to-one relationship.

If you were to set this up in Access’s relationship tool, you would see a 1 on each end of the line.

One-to-Many

The relationship for the vast majority of situations is one-to-many. A customer will make multiple purchases. In this case, one instance of entity CUSTOMER will have multiple relations with multiple instances of entity PURCHASES.

When you have this sort of relationship, you will see a 1 on the one side, and an infinity mark on the multiple side.

Many-to-Many

The many-to-many relationship is very rare. We won’t be using it in our little database. However, an instance of entity CUSTOMER can purchase multiple instances of entity BOOK; and entity BOOK can be purchased by multiple instances of entity CUSTOMER.

Many database designers feel that this should be avoided as much as possible because it confuses the role of keys too much. As an example, the primary key in tblCustomer would have to be included in tblBook, and the primary key in tblBook would have to be included in tblCustomer. It is tough to decide what is fully included where.

To overcome this problem, many designers use a junction table. This table handles the many side of the one-to-many relationship of each table. Adding this table has the potential of reducing a lot of complexity.

In our case, the junction table could handle the multiple books purchased by a customer, and the multiple customers who bought a book. Neither would be a primary key in the new table.

Using Access’s ability to easily define relationships, you won’t need to design programming to accomplish this.




Access VBA Programming
Microsoft Access VBA Programming for the Absolute Beginner
ISBN: 1598633937
EAN: 2147483647
Year: 2006
Pages: 214
Authors: Michael Vine

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net