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
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.
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.
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.