Getting to Know the Basics of Databases in Microsoft Excel

The Concept of Relationships Between Tables in Databases

In database management, the relationships between tables are an important basis for organizing data properly. In this way, we can correlate data from different tables to prevent duplication and improve efficiency in data management. Here is the explanation.

One-to-Many relationship

A one-to-many relationship occurs when one record in a table can be associated with many records in another table.

Example 😀 Customer data and invoice management:

• The Customers table stores customer information, such as ID and name.
• The Invoices table stores customer transactions, such as invoice numbers and total payments.

This relationship connects the Customer ID in the Customers table with multiple invoices in the Invoices table.

Customer Table:

Invoice Table:

Here, the customer with ID “001” has two invoices (INV001 and INV002).

One-to-One relationship

A one-to-one relationship occurs when one record in a table is connected to only one record in another table.

Example 😀 Employee database:

• The Employees table stores employee data, such as names and IDs.
• The Employee Details table stores additional details, such as ID number or address.

Employee Table:

Employee TableDetails:

Each employee has only one detailed data.

Many-to-Many relationship

A many-to-many relationship occurs when one record in a table can be associated with many records in another table, and vice versa. This relationship often uses an intermediate table (junction table).

Examples of 😀 Online course systems:

• The Student table stores student data.
• The Courses table stores a list of courses.
• Student’s intermediate tableCoursecourse connects students with the courses they are taking.

Student Table:

Course Table:

Student TableCourse:

A student can take multiple courses, and one course can be attended by many students.

Benefits of Inter-Table Relationships

1. Avoid Data Duplication

By breaking the data into interconnected tables, the same information doesn’t need to be stored repeatedly.

Example: A customer’s name is stored only once in the Customers table, even if the customer has multiple transactions.

2. Data Storage Efficiency

Relationships between tables reduce storage space usage because repetitive data is separated into a single entity.

3. Ease of Data Retention

Changes in data in one table automatically affect relationships in other tables. For example, changing the customer’s name in the Customers table will reflect the change in all related transactions.

4. Data Analysis Flexibility

Relationships between tables make it easier to combine data from multiple sources for analysis purposes, such as sales reports per customer.