Types of Database Relationships

Generally speaking, database relationships refer to the way data is organized across multiple tables where data in one table is referring to data in another table by some relation. Technically speaking, database relationship is a term that defines the arrangement of data across tables such that primary key of one table is referenced in another table as foreign key. Relational databases thus can be split across different tables and yet be linked by defining a relationship between the tables.

Let us take specific database relations with clear cut simple examples:

* One-to-One relationship

When each row in a table is related to one and only one row in another table, the relationship is known as One-to-One and Vice-Versa. Usually it results from the data arrangement where both the related columns are constrained as primary or unique key.

Consider the following example where Table A contains the saving account ids of customers in a bank, while Table B contains personal details of the customers. Since, one customer can have only one account id and similarly an account id can only be associated with one and only one customer - the relationship is known as One-to-One.

Table A:
id saving_acc_id acc_openedon cust_id
1 ALM3456 10-10-10 1
2 SDF2322 10-11-10 2

Table B:
cust_id firstname lastname address
1 Pankaj Pal Bangalore
 2 Rajan Malhotra Delhi


One-to-One relationship doesn't serve any benefit and almost always it becomes an overhead in terms of performance as we need to perform a JOIN operation to return the data. Thus it makes sense to club the two tables together as:

Table A:
cust_id firstname lastname address saving_accid acc_openedon
 1 Pankaj Pal Bangalore ALM3456 10-10-10
 2 Rajan Malhotra Delhi SDF2322 10-11-10


However, it might make sense to keep them separate in certain scenarios where most of the queries are on the personal information of a customer and not on the account details. This reduces the overhead to retrieve account details whenever a query is performed on the personal details.

* One-to-Many relationship

In a one-to-many relationship, each row in a table is related to one or more rows in another table. This is the most common form of relationship that exists in relational databases. This arrangements allows us to avoid keeping duplicate data in one single table. We can keep the frequently sought information in one table and then refer it through the use of foreign key in another table.

Take a look at the following example - Table A refers to the personal detail of customers in a bank, while Table B refers to the transactions done by customers.

Table A:
cust_id firstname lastname address saving_accid acc_openedon
 1 Pankaj Pal Bangalore ALM3456 10-10-10
 2 Rajan Malhotra Delhi SDF2322 10-11-10


Table B:
id cust_id date type amount
 1 1
 10-10-10 Credit 10000
 2 2 10-11-10 Credit 10100
 3 1 11-10-10 Debit 525


Here, each customer can have one or many transactions under his account, but each specific transaction is only done on behalf of a single customer. Hence, the relationship from Table A to Table B is known as One-to-Many relationship. The benefit of this approach is that, by keeping the transactions in a separate table we are avoiding the duplicacy of keeping the complete details of a customer in the same table. If one wish to refer to details of customer involved, he can fetch the same from Table A using foreign key - 'cust_id' defined in Table B.

Note: Looking at this relationship from reverse angle - Table B has a many-to-one relationship with Table A.

* Many-to-Many relationship

When one or more rows in a table are related to one or more rows in another table - this relationship is known as many-to-many relationship. Maintaining such relationships in relational database becomes a bit tricky and it often calls for the need of intermediate tables.

Let us again consider the example of customers in a bank. Table A represents personal information of customers and Table B represents services that the bank offers. For the simplicity of discussion let us consider 'Saving account' and 'credit card' as two services offered by the bank.

Table A:
cust_id firstname lastname address
1 Pankaj Pal Bangalore
 2 Rajan Malhotra Delhi


Table B:
svc_id svc_name
1 Savings
2 Credit Card


Now, to keep the records we can add multiple entries in the Table B for each combination of customer and and the services he is availing. But using this approach it will become very difficult to maintain the records in future. For example, if some policy gets changed for Credit cards, then the same needs to be updated across all the rows in Table B that refer to credit card services.

So to avoid all this effort, we need yet another table - Table C. We will keep the multiple records pertaining to each individual customer in this table and refer to customer and service using foreign keys:

Table C:
cust_id svc_id
1 1
1 2
2 2


Looking at Table C, we can see that each customer in Table A can be linked to one or more services in Table B. Similarly, each service in Table B can be linked to one or more customers from Table B. Hence the term - many-to-many relationship.

* Self referencing relationship

When a row in a table refers to another row in the same table, it is known as self-referencing relationship. Let us assume that an existing customer in a bank can refer someone to become a customer in the same bank and in process he earns some credit. To maintain the same in database, let us consider a field - 'referred_by' in customer's personal information table :

Table A:
cust_id firstname lastname address referred_by
1 Pankaj Pal Bangalore 0
2 Rajan Malhotra Delhi 1


Data in Table A states that customer 'Rajan' was referred by customer with cust_id as '1' which points to 'Pankaj'. Also, customer 'Pankaj' wasn't referred by anyone else, hence his 'referred_by' id is pointing to non-existing customer id. Thus, we can clearly see that data in one row is referring to data in another row in the same table.


That's all what is required to understand the relationships between different tables in a relational database in very layman terms. Feel free to provide your comments / suggestion.

-Pankaj

comments powered by Disqus