Secrets of Access 2010 Database Design by Alison Barrows, Margaret Levine Young, and Joseph C. Stockman

Leave a comment

Here are the Five Commandments of database design, whether you use Access 2010 or another database program. A well-designed database makes maintaining your data easier.

  • Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a spreadsheet, you type information where you want it to appear when you print the spreadsheet, but databases work differently. In a database, you store information in tables based on the structure of the information. A piece of information may appear in lots of different reports, but you store it in only one field in one table.
  • Store information as it really exists, not as you want it to appear in a specific report. This is a corollary to the first rule. If you want book titles to appear in all uppercase (capital) letters in your purchase orders, Access can capitalize the titles for you. Store the book titles with correct capitalization so you aren’t stuck with them in all caps on every report. Access has lots of built-in functions that can adjust the way that text, numbers, and dates are formatted.
  • Garbage in, garbage out (GIGO). If you don’t bother to create a good, sensible design for your database — and if you aren’t careful to enter correct, clean data — your database will end up full of garbage.

    A well-designed database is easier to maintain than a badly designed one because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work.

  • Separate your data from your programs. If you create a database to be shared with (or distributed to) other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you can link these two databases together to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, or other stuff later without disturbing the data in the tables.
  • Back up early and often. Okay, this tip isn’t about design, but it’s too important to omit: Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly before making any major changes. Keep several backups, not just the most recent one, in case it takes a while to discover a problem.

Read more:

Knowing Just Enough about Relational Databases by Michael Rosenblum and Dr. Paul Dorsey

Leave a comment

Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:

  • What do people mean by relational database?
  • What is the Oracle relational database product?

What makes a database “relational”?

When a database is described as relational, it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.

For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1.

Table 1: Sample Employee Information

EmpNo Ename DeptNo DeptName
101 Abigail 10 Marketing
102 Bob 20 Purchasing
103 Carolyn 10 Marketing
104 Doug 20 Purchasing
105 Evelyn 10 Marketing

If you structure your data this way and make certain changes to it, you’ll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to “Advertising,” you would need to change the record of each employee in that department.

Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 2 and 3.

Table 2: A Sample Relational DEPT Table

DeptNo DeptName
10 Marketing
20 Purchasing

Table 3: A Sample Relational EMP Table

EmpNo EName DeptNo
101 Abigail 10
102 Bob 20
103 Carolyn 10
104 Doug 20
105 Evelyn 10

By using this structure, you can examine the EMP table to find out that Doug works in department 20. Then you can check the DEPT table to find out that department 20 is Purchasing. You might think that Table 1 looks more efficient. However, retrieving the information you need in a number of different ways is much easier with the two-table structure. Joining the information in the two tables for more efficient retrieval is exactly the problem that relational databases were designed to solve.

When the tables are implemented in the database, the information in the two tables is linked by using special columns called foreign keys. In the example, the DeptNo column is the foreign key linking the Department and Employee tables.

Tables 4 and 5 show another common database structure, namely a purchase order (PURCH_ORDER table) for an item and the information details associated with the purchase order (PURCH_ORDER_DTL table).

Table 4: A Sample Relational PURCH_ORDER Table

PO_Nbr Date
450 12/10/2006
451 2/26/2006
452 3/17/2006
453 6/5/2006

Table 5: A Sample Relational PURCH_ORDER_DTL Table

PO_Nbr Line_Nbr Item Qty Price
450 1 Hammer 1 $10.00
451 1 Screwdriver 1 $8.00
451 2 Pliers 2 $6.50
451 3 Wrench 1 $7.00
452 1 Wrench 3 $7.00
452 2 Hammer 1 $10.00
453 1 Pliers 1 $6.50

A purchase order can include many items. Table 5 shows that Purchase Order 451 includes three separate items. The link (foreign key) between the tables is the Purchase Order Number.

Understanding basic database terminology

A database consists of tables and columns, as described in the preceding section. There are some other terms you need to know in order to understand how databases work. A database is built in two stages. First you create a logical data model to lay out the design of the database and how the data will be organized. Then you implement the database according to the physical data model, which sets up the actual tables and columns. Different terminology applies to the elements of the logical and physical designs. In addition, relational database designers use different words from object-oriented (OO) database designers to describe the database elements. Table 6 shows the words used in each of these cases.

Table 6: Database Design Terminology

Logical/Relational Logical/Object-Oriented Physical Implementation
Entity Class Table
Attribute Attribute Column
Instance Object Row

The definitions of the words in Table 6 are as follows:

  • Entity: An entity corresponds to something in the real world that is of interest and that you want to store information about. Examples of entities include things such as departments within an organization, employees, or sales. Each specific department or employee is considered an instance of that entity. For example, in Table 3, Doug is an instance of the entity Employee. (In the OO world, Doug would be an object in the Employee class.)
  • Attribute: This word is used in both relational and OO databases to represent information about an entity instance or an object that will be tracked. An example of an attribute might be the birth date or Social Security number of an employee.
  • Entities (classes), their attributes, and instances (objects): These are implemented in the database as tables, columns, and rows respectively.

One additional important concept to understand when dealing with relational databases is the primary key. A primary key uniquely identifies a specific instance of an entity. No two instances of an entity can have the same primary key. The values of all parts of the primary key must never be null. The most common types of primary keys in relational databases are ID numbers. For example, in Table 3, the EmpID can be the primary key. Sometimes more than one attribute (or sets of attributes) can be used as a primary key. These attributes are called candidate keys, one set of which must be designated as the primary key.

Read more: