MS Access-related data

In this chapter, we'll learn the basics of the data. B efore we discuss and create relationships between different data, let's review why we need it. It's all back to normalization.

Normalization

Database normalization or simple normalization is the process of organizing columns (attributes) and tables (relationships) of a relationship database to minimize data redundancy. fe.

• Normalization is the process of organizing data in a database.

• This includes creating tables based on designed rules and establishing relationships between them to protect data and make databases more flexible by eliminating redundant and inconsistent dependencies.

Now let's look at the table below that contains the data, but the problem is that the data is quite redundant, which increases the chances of typing and inconsistent phrases during data entry.

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

Here, we have a Customers table, the second is the Orders table, and the third is the Cookies table.

The problem here is that just splitting the data in multiple tables will not help tell how the data in one table is related to the data in another. le.

Define the relationship

Relationships work by matching data in key columns, usually columns with the same name in two tables. I n most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row and has entries in the foreign key in the other table. ns. he type of relationship you create depends on how you define the relevant columns.

Now let's look at these three types of relationships -

One-to-many relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, the rows in Table A can have many matching rows in Table B, but the rows in Table B can have only one matching row in Table A.

For example, a customer and an order table have a one-to-many relationship: each customer can place an order, but each order comes from only one customer.

Many-to-many relationships

In a many-to-many relationship, the rows in Table A can have many matching rows in Table B, and vice versa.

You create such relationships by defining a third table, called a join table, whose primary key consists of foreign keys from table A and table B.

For example, customers and cookie tables have a many-to-many relationship, defined by a one-to-many relationship from these tables to the Orders table.

One-to-one relationships

In a one-to-one relationship, the rows in Table A can have no more than one matching row in Table B, and vice versa. If the related columns are primary keys or have unique constraints, a one-to-one relationship is created.

This type of relationship is not common because most of the information related in this way will be in one table. You can use one-to-one relationships -

• Divide the table into columns.
• Part of the isolation table for security reasons.
• Store short-lived data and simply delete the table.
• Stores information that applies only to a subset of the main table.