Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available
Before you build the tables and other objects that will make up your system, it is important to take time to design it. A good design is the keystone to creating a system that does what you want it to do effectively, accurately and efficiently.
NOTE: Some of the listed steps (determining tables, data fields and relationships) may cross and be repeated a few times when designing a relational database.
To determine the purpose of your system you need to know what information you want from the database (detailed scenario). From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the data fields).
So, make sure that you do answer the following questions:
The first step in creating a database is creating a plan that serves both as a guide to be used when implementing the database and as a functional specification for the database after it has been implemented. The nature and complexity of a database application, as well as the process of planning it, can vary greatly. In the first case, the database design may be little more than a few notes on some scratch paper. In the latter case, the design may be a formal document with hundreds of pages that contain every possible detail about the database.
NOTE: Modeling the structure on paper before opening computer and starting coding is highly recommendable. Planning may seem time-consuming up front, but not planning is twice as time-consuming later.
To determine the tables can be the trickiest step in the database design process. That is because the results that you want from the database (e.g. the reports that you want to print, the forms that you want to use, the questions that you want answered) don't necessarily provide clues about the structure of the tables that can produce them. In fact, it may be better to sketch and rework your design on paper first.
When you design your tables, divide up pieces of information by keeping following fundamental design principles in mind:
To identify fields with unique values (define table primary key) - In order to uniquely identify each individual record in a table and to be able to relate information stored in separate tables. By having a different primary key in each record you can tell two records apart - the goal of setting primary keys is to ensure each records uniqueness. This is called entity integrity in the database management.
The primary key types:
Notes:
So, every table must have a primary key - one or more data fields whose contents are unique to each record. When linking tables you link the primary key field from one (primary or 'parent') table to a field in another (related or 'child') table that has the same name, structure and data type. By matching the values from the primary key to the foreign key in both tables, you can relate two records.
The process of designing a relational database includes making sure that a table contains only data directly related to the primary key, that each data field contains only one item of data, and that redundant (duplicated) data is eliminated. The task of the database designer is to structure the data in a way that eliminates unnecessary duplication and provides a rapid search path to all necessary information. This process of specifying and defining tables, keys, columns and relationships in order to create an efficient database is called normalisation.
We use the normalisation process to design efficient and functional databases. By normalising, we store data where it logically and uniquely belongs. Normalisation process involves a few steps and each step is called a form. Forms range from the first normal form (1NF) to fifth normal form (5NF). There is also one higher level, called domain key normal form (DK/NF). However, we will cover the first 3 forms.
Normalisation is a complex process with many specific rules and different intensity levels. In its full definition, normalisation is the process of discarding repeating groups, minimizing redundancy, eliminating composite keys for partial dependency and separating non-key attributes.
In simple terms, the rules for normalisation can be summed up in a single phrase: "Each attribute (column) must be a fact about the key, the whole key and nothing but the key". Said another way, each table should describe only one type of entity (information).
A Properly normalised design allows you to:
Relational database theorists have divided normalisation into several rules called normal forms:
NOTE: You must be able to reconstruct the original flat view of the data. If you violate this rule, you will have defeated the purpose of normalising the database.
To determine the relationships between tables - Now that you've defined your information into tables and identified primary key fields, you need a way to tell the system how to bring related information back together in meaningful ways. To do this, you define relationships between tables. Relationship is an association between common fields (columns) in two tables. A relationship works by matching data in key fields. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. The kind of relationship that the system creates depends on how the related fields are defined. When you physically join two tables by connecting fields with related information, you create a relationship that is recognized by the system (Access). The specified relationship is important. It tells the system how to find and display information from fields in two or more tables. The program needs to know whether to look for only one record in a table or to look for several records on the basis of the relationship.
There are 3 relationship types:
When tables are linked (joined) together, one table is usually called 'parent' or 'primary' table ('one end' in the 1:M relationship and 'one end' (primarily created table) in the 1:1 relationship) and another table is called 'child' or 'related' table ('many end' in the 1:M relationship and 'one end' (subsequently created table) in the 1:1 relationship). This is known as a parent-child relationship between tables. Records in a primary table cannot be modified or deleted if there are related records in the 'child' table - there will not be an orphan (related) record without a parent (primary) record. Also, a new record cannot be added to the related table if there is no associated record in the primary table.
In addition to specifying relationships between two tables in a database, you also set up referential integrity rules that help in maintaining a degree of accuracy between tables. Setting referential integrity rules would prevent unwanted and accidental deletions and modifications of the 'parent' records that relate to records in the 'child' table. This type of problem could be catastrophic for any system. The referential integrity rules keep the relationships between tables intact and unbroken in a relational database management system - referential integrity prohibits you from changing existing data in ways that invalidate and harm the links between tables.
NOTE: Referential integrity operates strictly on the basis of the tables' key fields. It checks each time a key field, whether primary or foreign, is added, changed or deleted. If any of these listed actions creates an invalid relationship between two tables, it is said to violate referential integrity. Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or incorrectly change related data.
To refine the design - After designing the tables, fields, and relationships you need the time to study the design and detect any flaws that might remain. It is easier to change your database design now, rather than after you have populated the tables with data or created the relevant screens.
NOTE: Use Microsoft Access to create your tables. Specify relationships between the tables. Enter a few records of data in each table. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them.
To enter data and create other system objects - When you are satisfied that the table structures meet the design goals described here, then it's time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, macros, and modules that you may want.