Let's take a look at two order-entry scenarios:
- A customer wants to cancel an order that she's placed. If her address is in a separate table from her order, you can easily delete the order without deleting her address.
- Or you've just entered a pile of customer orders when your company changes its shipping methods. If the shipping methods are maintained in their own table, you don't have to update each individual order.
Access works with separate tables in relational databases, which can help you structure data efficiently, avoid wasted effort, and reduce headaches.
Relational databases store data in separate tables, based on subject matter, but the tables are brought together through relationships. For example, a table of customers is related to a table of orders by a customer ID field in each of those tables.
Typically, data should not be repeated in more than one table, except for such relating fields.
Some benefits of this method are:
- Efficiency You don't have to store redundant information, such as a customer's name or address, in every order that the customer places.
- Control It's easier to update, delete, and extend data in a well-structured database that doesn't contain duplication.
- Accuracy By avoiding repetition, you decrease the opportunity for errors. Right once, right everywhere.
- Data integrity You can add or remove fields or records in unique tables without affecting your data structure, and you will not need to redesign your database.
Separation protects your original structure. As you plan your tables, think of ways to structure your data so that it's easy to enter and maintain.
