Understanding Relationship in Database (RDBMS)

There are three type of relationships

1) One to one

2) One to many

3) Many to many

Say we have table1 and table2

For one to one relationship, a record(row) in table1 will have at most one matching record or row in table2

I.e. it mustn’t have two matching records or no matching records in table2.

For one to many, a record in table1 can have more than one record in table2 but not vice versa

Let’s take an example,

Say we have a database which saves information about Guys and whom they are dating.

We have two tables in our database Guys and Girls

Guy id	Guy name
1	Andrew
2	Bob
3	Craig

Girl id	Girl name
1	Girl1
2	Girl2
3	Girl3

Here in above example Guy ID and Girl ID are primary keys of their respective table.

Say Andrew is dating Girl1, Bob – Girl2 and Craig is dating Girl3.

So we are having a one to one relationship over there.

So in this case we need to modify the Girls table to have a Guy id foreign key in it.

Girl id	Girl name	Guy id
1	Girl1	1
2	Girl2	2
3	Girl3	3

Now let say one guy has started dating more than one girl.

i.e. Andrew has started dating Girl1 and say a new Girl4

That takes us to one to many relationships from Guys to Girls table.

Now to accommodate this change we can modify our Girls table like this

Girl Id	Girl Name	Guy Id
1	Girl1	1
2	Girl2	2
3	Girl3	3
4	Girl4	1

Now say after few days, comes a time where girls have also started dating more than one boy i.e. many to many relationships

So the thing to do over here is to add another table which is called Junction Table, Associate Table or linking Table which will contain primary key columns of both girls and guys table.

Let see it with an example

Guy id	Guy name
1	Andrew
2	Bob
3	Craig

Girl id	Girl name
1	Girl1
2	Girl2
3	Girl3

Andrew is now dating Girl1 and Girl2 and

Now Girl3 has started dating Bob and Craig

so our junction table will look like this

Guy ID	Girl ID
1	1
1	2
2	2
2	3
3	3

It will contain primary key of both the Girls and Boys table.

Why do we need one to one relationship when we can easily accommodate the values in a single row?

For isolating some sensitive data. Employee table with its salary information stored in a separate table.

Putting large data in a separate table say a BLOB or Image type in which we are saving an image or document.

This type of relationship is typically used to improve performance.

And for our One to Many and Many to Many relationship, they help in maintenance.

If we had been storing multiple values in a column it would have been very difficult to query that table.

For e.g. say we have hobby column in our student table

And hobby has multiple values

Student ID	Student Name	Hobby
1	Sandra	Swimming, Reading
2	Jans	Swimming, Rowing

What would be select query to find student name having hobby as reading or update swimming with dancing. It would be too difficult to write such a query.

So if we have these values separated in another table we can easily query it.

Bye

Discover more from Nishant Rana's Weblog

Subscribe to get the latest posts sent to your email.

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology ! View all posts by Nishant Rana

25 thoughts on “Understanding Relationship in Database (RDBMS)”

Pingback: aroosx » Understanding Relationship in Database (RDBMS)

hi nice example it is easy understand examples u have gave it is useful to me to imagine

Discover more from Nishant Rana's Weblog

Share It -

Related

Author: Nishant Rana

25 thoughts on “Understanding Relationship in Database (RDBMS)”

Please share your thoughts Cancel reply

Discover more from Nishant Rana's Weblog