// Not Sure Where to Start?
Access Database Design
Last updated: April 2026
A poorly designed Access database produces bad data. Not eventually, but from the first week of use: duplicate records, inconsistent entries, reports that cannot be trusted, and queries that return different results depending on who ran them. PCG designs Access databases from the table structure up, before a single form is built, so the data that comes out is accurate and the application does not need to be rebuilt in two years.
Why does database design matter more than the forms and reports people actually see?
The forms and reports are what users interact with. The table structure is what determines whether the data those forms collect is usable. A database with poorly designed tables produces data that cannot be reliably queried, reported on, or migrated to a larger platform when the business eventually needs one. Most Access databases that PCG is called in to rescue were built by starting with the form rather than the table structure. The forms look reasonable. The data underneath them is a mess.
In 2026, the cost of bad database design shows up in three ways: staff time spent manually reconciling records that the database should be reconciling automatically, reports that require manual cleanup before they can be trusted, and migration projects that take twice as long as they should because the source data has to be cleaned before it can move. PCG designs the structure first so none of those costs accumulate.
What are the two principles that drive every PCG database design?
Eliminate redundant data before it enters the system
The same piece of information stored in two places is not a backup. It is a source of inconsistency. When a customer's name appears in three tables and gets updated in only one, the database is now producing three different versions of a fact. PCG designs table structures that store each piece of information once, in the right place, with relationships that pull it wherever it is needed rather than copying it into multiple locations.
Enforce data integrity at the table level, not the form level
Validation rules applied only to forms can be bypassed. A user who imports a spreadsheet, runs a direct query, or uses a different form bypasses every validation rule that was not built into the table itself. PCG builds data integrity constraints into the table structure: required fields, data type enforcement, referential integrity between related tables, and default values where appropriate. The data is correct because the architecture requires it to be correct.
What does bad Access database design actually look like, and what does it cost?
The following design failures appear in the majority of Access databases PCG is asked to repair or migrate. Each one has a measurable operational cost that compounds as the database grows.
| Design Failure | What It Produces | Operational Cost |
|---|---|---|
| No primary keys on tables | Duplicate records accumulate with no reliable way to identify or remove them | Data deduplication required before any meaningful analysis or migration |
| Multiple pieces of information in one field | A "name" field containing first name, last name, and title; an "address" field containing street, city, state, and ZIP | Cannot sort, filter, or report on any individual component without manual parsing |
| Redundant data across multiple tables | Customer name stored in orders, invoices, and contacts tables independently | Updates in one place do not propagate; database produces conflicting facts about the same entity |
| No referential integrity enforced | Child records (orders, invoices) exist without parent records (customers, products) | Orphaned records corrupt reports and cause query errors that are difficult to diagnose |
| Calculated values stored as fields | A "total" field that stores a calculated result rather than deriving it from component values | Totals go out of sync when component values change; reports produce inconsistent results |
| All data in one table | A single flat table with hundreds of columns instead of normalized related tables | Performance degrades sharply as row count grows; migration to SQL Server requires full restructuring |
Every one of these failures is preventable at design time. Correcting them after data has accumulated typically takes longer than building the database correctly from the start, because the existing data has to be cleaned and restructured before it can move into a properly designed schema.
What does PCG's Access database design process actually involve?
PCG follows a structured eight-step design process for every Access database project. The process is not a checklist of abstract principles. Each step produces a concrete output that feeds into the next step and determines what gets built.
01
Define the purpose and the output
Before designing a single table, PCG establishes what the database needs to produce: which reports, which exports, which queries your operation depends on. The output requirements determine the table structure. A database designed without knowing what it needs to report will produce tables that cannot generate the reports without significant restructuring.
02
Inventory the existing data
PCG collects the data you are currently working with: spreadsheets, paper forms, existing databases, or verbal descriptions of what gets captured. This inventory identifies every field that needs to exist in the new design and surfaces data quality issues in the source material that need to be resolved before the new database is built.
03
Divide information into subject-based tables
Each table in a correctly designed database holds information about one subject. Customers in one table. Orders in another. Products in a third. PCG identifies the subjects the database needs to track, assigns each field to the correct subject table, and eliminates every instance of the same information appearing in more than one place.
04
Define columns and data types
Each piece of information becomes a column with a defined data type, length, and constraint. PCG ensures that composite fields are broken into their components (first name and last name as separate fields, not one), that data types match the actual data being stored, and that fields that should never be empty are marked as required at the table level rather than only at the form level.
05
Establish primary keys
Every table gets a primary key: a field or combination of fields that uniquely identifies each record. PCG selects primary keys that are stable (not likely to change), unique, and meaningful for the data model. Auto-number fields are used where natural keys do not exist or where stability is a concern.
06
Define table relationships and enforce referential integrity
PCG defines the relationships between tables using foreign keys and enforces referential integrity at the database level. This prevents orphaned records, ensures that related data stays consistent when records are updated or deleted, and gives the query engine the relationship information it needs to join tables correctly without performance penalties.
07
Test the design against real queries
Before any forms or reports are built, PCG tests the table structure against the output requirements identified in step one. Can the database produce the reports the business needs? Do the joins between tables return correct results? Are there any query scenarios that the current design cannot support? Problems identified at this stage cost a fraction of what they cost to fix after the database is populated with live data.
08
Apply normalization and finalize the schema
PCG applies database normalization rules to eliminate remaining redundancy and ensure the schema is structurally clean. The normalized schema becomes the foundation for form development, report building, and VBA automation. It also becomes the migration map if the database eventually moves to SQL Server, reducing migration effort significantly compared to migrating an unnormalized Access schema.
What does PCG need to design an Access database for your operation?
Database design starts with understanding the business process, not the technology. PCG needs to know what you are tracking, what decisions the database needs to support, and what the output has to look like. The design conversation takes one to two hours for most projects and produces a schema that both PCG and your team can review before any development begins.
- A description of what you are tracking. Customers, orders, inventory, compliance records, inspection findings, employee credentials: the subjects define the table structure. PCG needs to know every category of information the database has to manage.
- Samples of your existing data. An existing spreadsheet, a paper form, or a printout from a current system. PCG uses this to identify every field that needs to exist, surface data quality issues in the source material, and verify that the proposed design can accommodate the actual data rather than an idealized version of it.
- The reports and outputs you need. What does the database need to produce? Which reports does your operation run regularly? What data exports does your team use? The output requirements drive the table design as much as the input requirements do.
- Any future requirements you can anticipate. A database designed only for today's requirements often requires structural changes when requirements grow. If you expect to add users, increase data volume, or integrate with other systems within two to three years, PCG designs for those requirements now so the migration does not require rebuilding from scratch.
PCG designs the database. You own it. Every Access database design PCG delivers includes the complete schema documentation: table definitions, field specifications, relationship diagrams, and normalization notes. You receive this documentation along with the database itself at project completion.
If you want to modify the database later, hand it to another developer, or use the schema as the basis for a SQL Server migration, you have everything you need to do that without returning to PCG. That has been the standard since 1995.
1 Database design failure patterns documented from PCG repair and migration engagement audits across more than 200 Access databases assessed between 2015 and 2026.
2 Normalization methodology based on Codd's relational database normalization rules (1NF through 3NF) as applied to Microsoft Access table architecture.
Frequently Asked Questions
Yes, but the complexity depends on what is wrong with the current structure. Most design problems in existing Access databases, missing primary keys, redundant fields, improperly normalized tables, are fixable without losing data. PCG audits the existing schema, maps the data in its current state, designs the corrected structure, and migrates the data from the old schema to the new one. The process takes longer than building correctly from the start, but the data is preserved throughout.
For most small to mid-size Access projects, the design phase takes three to five business days from the initial scoping conversation to a reviewed and approved schema. Larger projects with multiple modules, complex relationships, or significant existing data to inventory take longer. PCG presents the schema for your team's review before any development begins, so design decisions are confirmed before they become development commitments.
Normalization is the process of structuring a database to reduce redundancy and ensure data integrity. For an Access project, it matters for two reasons. First, a normalized database produces consistent query results because each fact exists in exactly one place. Second, a normalized Access schema migrates to SQL Server cleanly when the business eventually needs a more robust platform. An unnormalized schema requires restructuring before migration, which adds weeks to the project and risk to the data.
Yes. PCG designs every Access database with the SQL Server migration path documented. The table structure, primary keys, relationships, and normalization approach are all compatible with SQL Server from the start. When the business grows past Access's practical limits, typically ten or more concurrent users or record volumes approaching 100,000 per table, the migration moves the data and the structure together without rebuilding the schema.
Database design as a standalone engagement, producing a complete schema with table definitions, relationship diagrams, and normalization documentation, typically runs between $1,500 and $4,000 depending on the number of tables, the complexity of the relationships, and whether existing data needs to be inventoried and analyzed. For most projects, the design engagement is included in the full development quote. PCG can scope the design phase separately if your team needs the schema reviewed before committing to full development.
PCG inventories all existing spreadsheets as part of the design phase, maps the fields across sheets to identify overlaps and conflicts, and designs a unified table structure that can accommodate all the data. The migration from multiple spreadsheets to a single database includes data cleaning to resolve inconsistencies between sheets: different naming conventions for the same entity, date formats that vary between files, and calculated fields that need to be replaced by stored values or derived queries. The output is one clean database with all your data in one place and in a consistent format.
Yes. Every PCG database delivery includes schema documentation: table definitions listing every field with its data type, size, and constraints; relationship diagrams showing how tables connect; and notes on the normalization decisions made during design and why. This documentation lets any qualified Access or SQL Server developer understand and modify the database without requiring PCG to explain it. That independence is the point.
A new design starts from requirements and produces a clean schema. A redesign starts from an existing database with accumulated data and structural problems, maps what exists, identifies what needs to change, and migrates the data into the corrected structure. Redesign engagements are more complex because the existing data constrains the options: some structural changes that would be straightforward in a new design require careful data transformation when the database is already populated. PCG scopes redesign engagements after an audit of the existing structure, not from a standard template.