Continuing our series of Tips – Tricks & Guidelines with Microsoft Access we share some additional items. The ongoing articles will continue to focus on tables within an Access database.
Database Tables and Table Relationships
What exactly are database table relationships and why would you want to use them? Designing a database with multiple tables can be particularly challenging. Not only do you have to determine all the database tables but understanding the concept of a database as far as multiple tables is a difficult undertaking.
Some people just give up on trying to do this, and quickly gravitate to MS Excel. Before long, they find themselves in a spreadsheet nightmare of having multiple spreadsheets linked together across the great chasm of a shared network. Individuals navigate to Excel, because they do not have the time or knowledge to build an Access database.
So, assuming you want to build a database in Access, then the table relationship window in Access can be extremely helpful. My professional opinion is don’t create anything in your database until you can map out all relationships using this feature. In the below figure, we see a standard relationship between a customer and an order.
This article will focus on the “one to many” relationship. What does this relationship mean and how is it used? In the above example, the customer information is stored as the “one” relationship and the orders are stored as the “many” relationship. Why would you want to store the customer information more than once? The orders or many side will only store the CustomerID more than once because a customer can order more than once.
For example, if a driveway sealcoat company blacktops your driveway. In this case, the customer is stored in the customer table and all the seal coat/order details will be stored in the orders table.
Two years from the first sealcoat, the driveway will need to be resealed again. The customer is already in the database, so a new order is created for the same customer. In the below form view of the orders shows the final development once the tables are created.
In the above example, the customer job information is the primary component of the one-to-many relationship. If a new job is needed for the same customer, all the user does is select the new job button in the below diagram.
Once the new job is added, then the customer summary screen changes to reflect the 2nd job for the same customer. See the below diagram.
So this takes us back to the table relationships, but also bridges the gap to why you setup relationships in the first place.
The above figure is the outcome of creating the tables and forms. The below figure is where it started when you are setting up the table relationships to begin with. The CustomerID in the customers table matches to an order in the Orders table.
Tip – Never begin to create any database entry forms in an Access database until you have mapped out all the table structure first.
The secondary items in the below diagram include the following:
- Enforce Referential Integrity – An order cannot be entered into the orders table until that said customer is created first. This prevents “lost” orders being created without a customer.
- Cascade Updated Related Fields – If the customer ID field value changes in the customer table, all associated customer ID values will also change in the orders table. This is more prevalent in a database that has product numbers or employee id values changing.
- Cascade Delete Related Records – If you delete a customer, all associated orders will also be deleted. Again, this prevents “lost” or “Orphaned” records from being left alone in the downstream tables.
In summary, designing a database with multiple tables is not an easy task. However, it is possible with a great deal of research and hard work, it can be done. It’s really a puzzle that you solve, and all the pieces are right in front of you when it comes to tracking the data within your day-to-day processes.
If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.