TYPES OF RELATIONSHIPS IN DATA MODELLING
Start by learning the meaning of the relational database. A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases consist of tables, columns, rows, keys, and relationship between tables. There are three types of relationships between the tables, and these are one to one, one to many and many to many relationships.
One to one (1:1) Relationship
One to one relationship means one entity in a table is related to just one entity in another table. It should be rare in any relational database design.
For instance, each student has only one address and each address
represents one student.
One to Many (1 : M) Relationship
One to many relationship means one entity in a table is related to one or many entities in another table. A one to many (1 : M) relationship should be the norm in any relational database design and is found in all relational
database environments.
For example, each customer has one or more orders.
Before proceeding many to many relationships, let’s look at the cardinality and connectivity. These are helpful to understand many to many relationship.
Cardinality and Connectivity
Cardinality describes the relationship between two data tables by expressing the minimum and maximum number of entity occurrences associated with one occurrence of a related entity. Either there is a relationship or not.
Connectivity is the relationship between two tables. It can be one to one or one to many.
Many to Many (M : N) Relationship
Many to many relationship means multiple entities in a table can be associated with more than one entity in another table.
For instance, each student can attend many courses and, each course can consist of many students.
Many to many relationship causes duplications in database. Duplications cause false results from queries. For instance, a company produces products, which consist of several components. One product is made up of many components, and one component can be in more than one product.
In the Entity-Relationship (ER) Diagram, “component_id” is specified as Primary Key in a component table, and Primary Key should be unique. Looking at the component table below, the primary key(component_id), component_name, and description are duplicated in order to record detailed information about each component.
Let’s explain a way to prevent the duplication while setting many to many relationship. The solution is creating a totally new table. It’s called as bridge table or join table.
Bridge table consist of “product_id” , “component_id” from related tables’ Primary Keys. “product_id” and “component_id” are both Primary and Foreign Keys the bridge table. Also, new columns can be added that are not available in the product table and component table.
Entity-Relationship (ER) Diagram helps to make and understand Entity Relationship (ER) Data Model. This site which is the below can be used for Entity Relationship (ER) Data Modelling with Entity-Relationship (ER) Diagram. This website helps to create and visualize diagram. The visualization makes it easier to understand the relationship between the tables. Also, this website is free and speedy. It has to many options to create diagram.
To sum up, relational database has ability to create meaningful information by joining tables. Joining tables help to understand the relationship between the data and tables. Besides, relational databases eliminate data redundancy. Relational database has three relationship between tables. Many to many relationship is harder to understand than other relationships. The bridge table makes the database and relationship easy to understand, and it prevents data redundancy.
Recently, the usage of relational databases is widespread in various domains. Understanding the relational database systems is essential in both design and usage phases. Entity-Relationship (ER) Model helps understanding relational databases and it is crucial to visualize them. Entity-Relationship (ER) Diagram is valuable while creating Entity-Relationship (ER) data modeling. Entity-Relationship (ER) Diagram provides building error-free Relational Database Design.
