ERD Best Practices | Resolving DBMS Relationships Issues

ERD University Management System

Previously , we resolved a case study by creating an ERD for University Management System . Today , we will focus on ERD best practices and resolving dbms relationship issues . This can make our life a whole lot easier . So far , we have established the fact that an Entity-relation diagram is the first step in designing a DBMS . After an ERD , we usually move forward with relational modelling . Hence , it’s important that our ERD should be very well documented and well designed and it should be able to avoid some of the major pitfalls such as anomalies in dbms.

RESOLVING MANY-TO-MANY RELATIONSHIPS :

When we talk about resolving relationships in DBMS , we are adapting pro-active approach and making our design error-prone even before implementing the design. How’s that  for the best practices. Never allow a relationship that is many-to-many , instead reduce it one-to-many and create an associative Entity You’re probably like for a reason , pretty understandable let’s go for it. 

ERD University Management System

You can see Student and Course has many-to-many relationship between them .

Problem

In DBMS , one objective is that data is organised in a way that data should be able to identify all records in a table with minimum redundancy . Student and Course will have a problem that there could be a repetition of set of keys when they will be joined.

Many-To-Many means many courses related to more than one student and vice versa. If you want to see How many courses does a student takes. You will end up with something like this

How many more columns will you add as still you’re not sure as exactly how many more courses a Student will take. Will you add more columns ? because you cannot have more rows for Faiz as Student_ID is  Primary Key.

Solution:

Create a bridge table ( junction table ) also known as Associative entity and pass both Student_ID and Course_ID as Foreign key into it with a Primary Key of its own. The above model will now look like this

MULTI-VALUED ATTRIBUTES

Change multi-valued attributes to separate entities and provide its foreign key in place of  attribute . This way you can have a better control over such attributes.

These two are the main practices that no matter what you should follow so that you don’t end up with problems in future , where you have to change the design again and again.

We have applied these rules to resolve dbms relationships issues in the ERD Case Study | University Management System Part 2 System.. Then we’ll do a relational modelling for it too. If this was helpful , share with your friends and follow us for more articles to come .

Advertisements

Author: Awais

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

One thought on “ERD Best Practices | Resolving DBMS Relationships Issues”

Leave a Reply