Pindipin Blog

Database Relations

The process of connecting data between tables so that they are related and useful together is known as database relationships. Consider a database as a filing cabinet with different documents in each table. Relationships facilitate more effective data retrieval by connecting the information in the drawers.

In a database, relationships come in three primary forms:

1. The relationship between each record in table A and one record in table B is one-to-one (1:1). One individual, for instance, only has one ID number
2. One record in table A may be connected to numerous records in table B through the One-to-Many (1:M) relationship. For instance, a single consumer may place numerous orders.
3. Many-to-Many (M:M) – Many records in table A can relate to many records in table B. For example, students and courses—a single student can take many courses, and a single course can be taken by many students.

A table's column that links data to another table is called a foreign key. By restricting the values that can be input based on information from related tables, a foreign key guarantees data integrity.

Here are some common types of foreign keys:

1. Simple Foreign Key - A simple foreign key relates to the main key in another database and consists of just one column.
2. Composite Foreign Key - consists of a number of columns that work together to establish a connection with another table's primary key.
3. Self-Referencing Foreign Key - commonly used for hierarchical structures like categories or manager-employees, it refers to the primary key in the same database.
4. Cascading Foreign Key - Has rules such as CASCADE that can delete or update related data in another table when the parent data is changed.