Scaffold your ideas into application
Get your free alpha access now.
Only 445 left, Hurry!
11 Aug 2016

RDBMS Relationships

Relationships in RDBMS are represented as ONE TO ONE,ONE TO MANY,MANY TO ONE,MANY TO MANY, in this article we would explore all the relations with examples.

Lets try to represent the same model in a RDBMS, for this example we would be using MySQL but the concept should be pretty much same in any RDBMS.

CREATE TABLE USER ( 
USER_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL  );

CREATE TABLE ROLE(  
ROLE_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL);

CREATE TABLE PERMISSION(
PERMISSION_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL);

This model so far allows us to create and store User, Role and Permission but still does not allow us to link them, for which we would need to create foreign keys. Foreign Keys are references to primary keys of foreign tables which is why they are called foreign keys. Before we jump into creating foreign keys, we would need to define the relationships, in database there are four kinds of relationships, ONE TO ONE, ONE TO MANY, MANY TO ONE, MANY TO MANY.

ONE TO ONE is a relation when one object can have only one instance of another object. for example in most of the cases, a User can have only one Manager or Department or Company. This kind of relationship is ONE TO ONE. In most of the cases you can specify this kind of relation as has a kind of relation.

ONE TO MANY is a relation when one object can have multiple instance of another object. In our example a Role can have multiple Permissions instance. This kind of relationship is ONE TO MANY. In most of the cases you can specify this kind of relation as has many or is collection of kind of relation. In most of the case the ONE side of ONE TO MANY relation is called Parent and the other side is called Child

MANY TO ONE is a relation when one object is a part of another object, this is reverse of ONE TO MANY, each ONE TO MANY would result in a reverse MANY TO ONE from child object. In most of the cases you can specify this kind of relation as belongs to kind of relation.

MANY TO MANY is a relation when one object can have multiple instance of another object, while other object can also have multiple instances of this object. In our example User can have multiple Role while same Role can be assigned to multiple Users, i.e Role has multiple User.

Lets try to first create a foreign key from Role to Permission. A ONE TO ONE relation is represented by a reference column on either of the table, usually the parent side, while ONE TO MANY is represented by a reference column on child table. Lets look at the example

USER TABLE

USER_IDNAME
1John Doe
2Jane Doe

ROLE TABLE

ROLE_IDNAME
1Administrator
2HR

If the relation between User and Role were to be Many to One, i.e. User belongs to a Role, we can add a column to User table say AssignedRole which would have reference to the ROLE_ID from Role Table, we can do that by running a query

ALTER TABLE USER ADD COLUMN ASSIGNED_ROLE BIGINT,
ADD FOREIGN KEY (ASSIGNED_ROLE) REFERENCES ROLE(ROLE_ID);

Now our table populated should look like this.

USER TABLE

USER_IDNAMEASSIGNED_ROLE
1John Doe1
2Jane Doe2

ROLE TABLE

ROLE_IDNAME
1Administrator
2HR

So with this data, we can figure out that John Doe is an Administrator while Jane Doe is a HR, However if we have a situation where both of them have another Role this data model would not accommodate it. For this we would need an additional Table, lets call it USER_ROLE_ASSOCIATION, This table when populated should look like this.

USER TABLE

USER_IDNAME
1John Doe
2Jane Doe
3Tim Doe

ROLE TABLE

ROLE_IDNAME
1Administrator
2HR
3Database Administrator

USER_ROLE_ASSOCIATION

USER_IDROLE_ID
11
13
22
33

With above data, it seems John is a developer as well as an Database Administrator, while Tim is also a fellow Database Administrator. This is an example of MANY TO MANY relation.

Lets add some query to complete the script for creating the association table.

CREATE TABLE USER_ROLE_ASSOCIATION (
USER_ID BIGINT NOT NULL REFERENCES USER (USER_ID),
ROLE_ID BIGINT NOT NULL REFERENCES ROLE (ROLE_ID)
)

In the next part we would discuss the Object Relation Impedence Mismatch, which comes when we try to match this database model to OOP domain model and how ORM helps bridge this gap.


Stats:
110 views
Scaffold your ideas into application
Get your free alpha access now.
Only 445 left, Hurry!

Angular Directives Demystified

Directives are the most misunderstood aspect of angular JS. Learn how directives can help you keep your code clean.

Third wave of digital transformation

Third wave of digital transformation

Angularjs Tutorial: Understanding Scope

Scopes are one of the most important and rarely understood concept in angular, in this part we explore how scope works in angularjs.

Comments:

Leave your comments