Standardize databases in Access - Relationships between tables

In this article, TipsMake.com will work with you to learn about Standardizing databases and an overview of relationships, data links between tables.

Standardize the database

Database Normalization is the process of organizing the columns (attributes) and tables (relations) of the relational database to be reasonable to minimize data redundancy.

This process divides data across multiple tables to improve the overall performance, integrity, and life of the database.

In short, you can understand:

1. Standardization is the process of organizing data in a database.
2. The process involves creating tables and establishing relationships between tables according to rules designed to protect data, making the database more flexible by eliminating inconsistencies and inconsistencies. .

For example:

Please follow the table in this example, the data here can be judged to be redundant, more likely to mistakenly type and generate inconsistencies in the input process.

To solve this problem, we need to restructure our data and divide it into multiple tables to eliminate some redundancy as shown in the following three tables.

Here, we have 1 table for Customers , 1 Product table and 1 Order table.

If only splitting data into multiple tables is simple, nothing can be done because the tables are not linked, there is no way for the data of this table to be related to the other table. To make the data connection in many of these tables, you will need to add Foreign Key (Foreign Key) to the Order table .

Identify relationships between tables

Relationship between tables works by matching data in columns, usually columns of the same name, present in both tables. The primary key column in the table will provide a unique ID for each record (each row) and a foreign key in another table.

There are 3 types of relationship settings:

1-1 relationship

Relation 1-1 is the relationship between two tables that a record of table A relates only to a record of table B and vice versa.

One-to-one relationships are created when both connection fields are primary keys or have unique constraints.

This type of relationship is not common. You can use 1-1 relationship in the following cases:

1. Divide a table into multiple columns.
2. Isolate part of the table for security reasons.
3. Storing data is short-lived and can be easily deleted by deleting the table.
4. Information store only applies to a subset of the main table.

1-n relationship (one-to-many)

The 1-n relationship is the most common type of relationship. In this relationship, a record in table A can have multiple matching records in Table B, but a record in Table B has only one matching record. In table A. It can be understood, the primary key in table A can be copied multiple times in another table.

For example, the Customers and Orders table has a one-to-many relationship: each customer can place multiple orders, but each order only comes from one customer.

Relationships (many-many)

The nn relationship is the relation between two tables, a record of table A can have multiple records matching in table B and vice versa.

To create such a relationship, define the third table, called a join table, with the primary key including foreign keys from both Table A and Table B.

For example, the Customers table and the Product table have nn relationships defined by a 1-n relationship from them to the Orders table .

Previous article: Alternate Criteria in Access 2016

Next lesson: Alternate Criteria in Access 2016