Table Relationships

 Before moving to the topic “Table Relationship”, we would have a look on the definition of Database, Table, Column and Row, as these entities participate in relationships.

Database

Database is an organized collection of data for fast processing on these data. These data are arranged in such form that enables fast retrieval of information and manipulation on these data. In RDBMSs, database organizes its data in relational form only using tables, column and rows. We can define relationships between these tables.

Table

A table is a collection of related data in a structured form using columns and rows within a database. It organizes its data using vertical columns and horizontal rows, intersection of a column and a row is known as a cell which stores the actual value in a table. Each column in a table has a unique name and no two columns of a table can have same name. A table have a defined number of columns with any number of rows. Rows can be identified with the help of primary key, a subset of columns which can identify each row uniquely.

Example: Microsoft Excel sheet in a MS Excel workbook is like a large table which operates with columns and rows.

Column

A column in a table stores a specific category of information. It is also known attributes or fields. Name of columns are unique in a table and table can have a defined number of columns only. For example, Employee Table can have Name, Address and Contact as columns, to categorize the related information.

Row

Rows are also known as tuples or records and represents a single data item in a table. Each row in Employee table represents one employee.

Database,Table.Column,Row

Table Relationship

In RDBMS, relationship defines a connection between two tables by matching data in key columns. For example, in above image, you can see that an employee works in a department. Relationships depends on the cardinality, where cardinality is the degree of relationship which determines the relationship between two tables. It determines how an entity from one table is related to the single entity of another table. There are three types of relationships as:

1. One to One (1 : 1)
2. One to Many (1 : m)
3. Many to Many (m : m)

Lets discuss each relationship in detail:

One to One Relationship

When one row from table T1 can have only one matching row in table T2, relationship between table T1 and table T2, is a one-to-one relationship. For example, one employee from “Employee Table” can have only one driving license detail in “Driving License Table”. This is an uncommon relationship, as such kind of information can easily be accommodated in one table. But, we can choose this in some scenario like; Splitting a wide table, in two tables, for maintenance and / or any security purpose.

One to one relationship

One to Many Relationship

One to Many relationship is the most common relationship in use. In this relationship, a row from table T1 can have multiple matching rows in table T2. Primary key of table T1 is used as a foreign key in table T2 to relate the rows. For example, an Employee can work on multiple projects, which would yield multiple matching rows in “Project Detail” table for an employee from “Employee” Table.

One to many relationship

Many to Many Relationship

A row from table T1 can have multiple matching rows in table T2 and a row from table T2 can have multiple matching rows in table T1. For example, an Employee from “Employee” table can opt for multiple policies from “Policy” table, and one policy from “Policy” table can be opted by multiple employees of “Employee” table. We need a bridge table to implement many-to-many relationship. This bridge table keeps references of primary keys from both the tables.

Many to many relationship