Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 36 ยท Design and Normalization

The Database Design Process

Design an Access database before you build it. Gather requirements, find entities and attributes, draw relationships, choose keys and naming conventions, and test with sample data.

The most common reason an Access database becomes painful is not a technical limit; it is that someone opened a blank table and started typing columns. A database that is designed first is easy to query, hard to corrupt and cheap to extend. A database that grew by accident is full of repeated data, empty cells and workarounds. This lesson is a repeatable design process you can finish on paper or a whiteboard before touching Access. The following lessons then refine the design with normalization and keys and integrity.

Step 1: define the purpose and the questions

Write two or three sentences about what the database is for and who will use it. Then list the questions it must answer and the reports people expect. Good questions are specific:

  • Which customers have not ordered in 90 days?
  • How many copies of each book are on loan right now?
  • What is the total of unpaid invoices per month?

Every question tells you which data you must store. If nobody needs to know it, do not store it. If somebody needs it and you did not store it, you cannot add it back for past records.

Step 2: gather the data you have

Collect the real documents in use today: the spreadsheet, the paper order form, the invoice, the sign-in sheet. Underline each distinct piece of information (customer name, product code, order date, quantity...). These are candidate fields. This step also exposes how the work really happens, including the exceptions (a customer with two addresses, a product with no price yet).

Step 3: find the entities (tables)

An entity is a thing you keep information about: a customer, a product, an order, an employee. Each becomes a table. A useful rule: if a piece of information describes a different thing than the one you are listing, it belongs in another table. A customer's phone describes the customer; a product's price describes the product, not the order (although you will copy the price into the order line later, as history).

Group your underlined items by what they describe:

Entity Attributes
Customer name, address, email, phone
Product name, category, current price
Order date, status, customer
Order line order, product, quantity, price at the time

Add lookup tables for lists of fixed choices: status, category, country. They keep spelling consistent and let you change a label in one place.

Step 4: define attributes and data types

For each field decide the Access data type and the rules:

Kind of data Data type Notes
Names, codes, phone numbers, ZIP Short Text text, even if it looks like a number
Notes, descriptions Long Text
Whole numbers, counts Number (Long Integer)
Money Currency exact to four decimals; do not use Double for money
Dates and times Date/Time (or Date/Time Extended) store dates as dates
Yes/no flags Yes/No
Files, photos Attachment
Automatic id AutoNumber for surrogate primary keys

Also decide which fields are required, which have default values, and which values are valid (for example, quantity greater than zero). These become field properties and validation rules in Tables and Relationships.

Store the smallest useful pieces: FirstName and LastName rather than one Name field if you will ever sort by last name; separate City, PostalCode and Country rather than a single Address blob if you will report by city. Never store a value you can calculate (LineTotal = Qty * Price, age from birth date); calculate it in a query so it can never be wrong.

Step 5: choose primary keys

Every table needs a primary key: a field (or fields) whose value identifies exactly one row and never changes. A natural key comes from the data (an ISBN, a product SKU) and a surrogate key is an invented one (an AutoNumber). Surrogate keys are simple and stable, and they are a safe default; use a natural key only when it is truly unique and permanent. Email addresses and names change, so they are poor keys.

Step 6: draw the relationships

Decide how each pair of tables relates:

  • One-to-many (the most common): one customer has many orders; each order belongs to one customer. The "many" table holds a foreign key pointing at the "one" table's primary key (Orders.CustomerID).
  • Many-to-many: an order contains many products, and a product appears in many orders. You cannot store this in either table; add a junction table (OrderLines) with a foreign key to each side. The junction table often holds data about the pairing too (quantity, price).
  • One-to-one: rare; a person and a passport record, or a split table to hide sensitive fields.
erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDER_LINE : contains
  PRODUCT ||--o{ ORDER_LINE : "appears in"
  CATEGORY ||--o{ PRODUCT : groups

An entity-relationship sketch: a many-to-many between orders and products becomes ORDER_LINE.

Step 7: test the design with sample data

Before building forms, create the tables (in Access or any SQL tool), enter 10 to 20 realistic rows, and run your questions as queries. If a question is hard or impossible to answer, the design has a hole. This SQLite example builds the order design above and answers a question that mixes all four tables:

SQL Test a design with sample data

Press Run (or Ctrl + Enter) to see the result here.

The answer needed four tables and three joins, and no value was typed twice. That is the sign of a healthy design.

Naming conventions

Pick conventions and stay consistent: singular or plural table names (Customer or Customers, not both), no spaces in names (they force square brackets everywhere), a clear key naming (CustomerID), and object prefixes in the Navigation Pane (tbl, qry, frm, rpt, mcr, bas for standard modules). Avoid reserved words such as Name, Date, Order and Group as field names: Access accepts them but they confuse queries and VBA (Orders and OrderDate are safer).

Common design mistakes

  • Several values in one cell ("Math, Physics"), or repeated columns (Phone1, Phone2, Phone3): move them to a child table.
  • Storing calculated values and then letting them go stale.
  • Using an editable, changing field (name, email) as the primary key.
  • No lookup tables, so the same thing is spelled five ways.
  • Skipping the design step because "it is just a small database"; small databases grow.

Test your own sketch with this checklist: purpose written down, questions listed, every table has a key, every relationship has a foreign key, many-to-many resolved, nothing calculated stored, sample data loaded, questions answered.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A many-to-many relationship between orders and products is implemented with:

    A junction table such as OrderLines holds one row per order-product pair.
  2. 2. Which should you store in a table?

    A price in the order line preserves history. Calculated values should be computed in queries, and separate name parts allow sorting.
  3. 3. Which data type should you use for money in Access?

    Currency uses exact fixed-point arithmetic and avoids rounding surprises.
  4. 4. Why is an email address a poor primary key?

    Keys should be stable; a surrogate AutoNumber is a safe default.

Summary

  • Design on paper first: purpose, questions, data, entities, attributes, keys, relationships, test data.
  • One entity per table; resolve many-to-many with a junction table; do not store calculated values.
  • Use sensible data types (Currency for money, text for codes) and consistent names without spaces.
  • Prove the design by answering your real questions with sample data.

Next: Normalization in Access