Database Design Steps

Database design is more art than science.   While it is true that a properly designed database should follow the normal forms and the relational model, you still have to come up with a design that reflects the business you are trying to model.   Relational database design theory can usually tell you what not to do, but it will not tell you where to start or how to manage your business.   This is where it is essential to understand the business (or other scenario) you are trying to model.   A well-designed database requires business insight, time, and experience.   Above all, it should not be rushed.

To assist you in the creation of databases, we have outlined the following 20-step approach to sound database design:

  1. Take some time to learn the business (or other system) you are trying to model.   This usually means meeting with the people who will be using the system and asking them lots of questions such as, "What is the nature of your business?" or "What do you sell?" or "What information is vital to your business?"
  2. On paper, write out a basic mission statement for the system.   For example, you might write something like, "This system will be used to take orders from customers and track orders for accounting and inventory purposes."   In addition, list the requirements of the system.   These requirements will guide you in creating the database schema (the definition of the tables) and business rules.   Create a list that includes entries such as, "Must be able to track customer addresses for subsequent direct mail."
  3. Turn off the computer and rough out on paper the data entry forms.   That's right - there is no substitute for working with pencil and paper through the many iterations to make sure you get this part right.   The specific approach you take will be guided by the state of any existing system:
    • If this system was never before computerized, take the existing paper-based system and rough out the table design based on these forms.   It is very likely that these forms will be non-normalized.
    • If the database will be converted from an existing computerized system, use its tables as a starting point.  However, remember that the existing schema will probably be non-normalized.   It is much easier to normalize the database now rather than later.   Print out the existing schema, table by table, and the existing data entry forms to use in the design process.
    • If you are starting from scratch (i.e. for a brand-new business), rough out on paper the forms you envision using.
  4. Based on the forms you created in step 3, rough out your tables on paper.   (Yes, again on paper!)   If normalization does not come naturally (or from experience), you can start by creating one huge, non-normalized table for each form you will later normalize.   If you are comfortable with normalization theory, try to keep it in mind as you create your tables, remembering that each table should describe a single entity.
  5. Look at your existing paper or computerized reports.   (If you are starting from scratch, rough out the types of reports you would like to see on paper.)   For existing systems that are not currently meeting user needs, it is likely that key reports are missing.   Create them now on paper.
  6. Take the roughed-out reports from step 5 and make sure the tables from step 4 include this data.   If information is not being collected, add it to the appropriate tables or create new ones.
  7. On paper, add several rows to each roughed-out table.   Use real data if at all possible.
  8. Start the normalization process.   First, identify candidate keys for every table and, using the candidates, choose the primary key.   Remember to choose a primary key that is minimal, stable, simple, and familiar.   Every table must have a primary key!   Make sure the primary key will guard against all present and future duplicate entries.
  9. Note foreign keys also, adding them if necessary to related tables.   Draw joins between the tables, noting whether they are 1->1 or 1->M. If they are M->M, create linking tables.
  10. Determine whether the tables are in First Normal Form.   Are all fields atomic?   Are there any repeating groups?   Decompose if necessary to meet 1NF.
  11. Determine whether the tables are in Second Normal Form.   Does each table describe a single entity?   Are all non-key columns fully dependent on the primary key?   Put another way, does the primary key imply all other columns in each table?   Decompose to meet 2NF.   If the table has a composite primary key, you should, in general, decompose the table by breaking apart the key.
  12. Determine whether the tables are in Third Normal Form.   Are there any computed columns?   If so, remove them.   Are there any mutually dependent non-key columns?   Eliminate mutually dependent columns by breaking out lookup tables.
  13. Using the normalized tables from step 12, refine the joins between the tables.
  14. Create the tables using Access.   Create the joins between the tables using the Edit Relationships command.   Add some sample data to the tables.
  15. Create prototype queries, forms, and reports.   While you are creating these objects, design deficiencies should become obvious.   Refine the design as needed.
  16. Bring the users back in.   Have them evaluate your forms and reports.   Are their needs met?   If not, refine the design.   Remember to renormalize if necessary (see steps 8-12).
  17. Go back to the Table Design screen and add business rules.
  18. Create the final forms, reports, and queries.   Develop the application.   Refine the design as necessary.
  19. Have the users test the system.   Refine the design as needed.
  20. Deliver the final system.

This list of 20 steps does not cover every facet of the design process, but you may find it useful as a framework from which you can start.   (This approach is based on the writings of MS-Access developer and database design wizard Michael Hernandez.)


We may be reached through any of the ways listed on the Contact Us web page.