a blog for those who code

Wednesday 20 August 2014

Understanding Relationships between tables

In this post we will learn about relationships between tables. You might have heard about the term relational databases, the term was coined because this kind of database was developed using mathematical set theory (because relation is part of set theory).

Two or more tables are said to have a relationship between them if the records they contain are associated with each other. Lets understand the above line by an example, states are associated with countries that means, in a database, a State table would have a relationship with a Country table. The importance of using relationship in tables is that, it makes data easier to update and allow only valid data to be inserted in a table.


StateID
StateName
CountryName
DateAdded
1
A
Z
17/8/2014
2
B
Z
17/8/2014
3
C
X
17/8/2014
4
D
Z
17/8/2014

Now, in the above example there are some problems with the design. Firstly, if you want to change the Country Name from Z to L. You would need to look through every row in the State Table as well as in any other table where Country Name is being referenced. Secondly, if Country X doesn't exist any more but in the state table you still have the reference of country table.

These above problems are very easy to fix using relationship between tables. It's just a matter of creating the right relationship between two tables and creating the right foreign key constraint over the relationship. Establishing a relationship between table means creating foreign key (copying one table's primary key column into the second table).

One-to-One Relationships

When a row in one table is associated with only one row in another table, and a row in that table can be associated with only one row in the first table, those two tables are said to have one-to-one relationship. To create these type of relationship the primary key of a table can be made primary key of another table.

One-to-Many Relationships

When a row in one table is associated with many rows in another table, but a single row in that table can be associated with only single row in the first table, those two tables are said to have one-to-many relationship. To create these type of relationship you need a primary key of one table to be a foreign key of another table.

Many-to-Many Relationships

When rows in one table can be associated with many rows in another table, and many rows in that table can be associated with many rows in the first table, those two tables are said to have many-to-many relationship.
To create these type of relationship you need to create two one-to-many relationship in a table.

Please Like and Share the Blog, if you find it interesting and helpful.

You may also like :

Casting with is and as operators in C#
Definition and Use of Indexes
Dot Net : All types are derived from System.Object
All you want to know about Common Language Run Time (CLR)
Null Coalescing operator (??) in C#
All about MongoDB Collections
How to improve Asp.Net Application Performance - Part 2
Write text files without Byte Order Mark.
Dictionary Vs Hashtable
C# NullReferenceException : Object reference not set to an instance of an object

No comments:

Post a Comment