ERD Best Practices | Resolving DBMS Relationships Issues
February 5, 2018
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.
You can see Student and Course has many-to-many relationship between them .
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.
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
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.
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!