In ER model, entities have attributes which can be of various types like single-valued, multi-valued, composite, simple, stored, derived and complex. But relationships can also have attributes associated to them. Generally it is not recommended to give attributes to the relationships if not required because while converting the ER model into Relational model, things may get complex and we may require to create a separate table for representing the relationship. Let us see various cases and when we need to give attributes to the relationship with the help of examples:
1. One to one relationship:
In an organisation an employee manages a department and each department is managed by some employee. So, there is a total participation of em>Department entity and there is one to one relationship between the given entities. Now, if we want to store the Start_Date from which the employee started managing the department then we may think that we can give the Start_Date attribute to the relationship manages. But, in this case we may avoid it by associating the Start_Date attribute to either Employee or Department entity.
2. One to many relationship:
In an organisation many employees can work for a department but each employee can work for only a single department. So, there is a one to many relationship between the entities. Now if we want to store the Start_Date when employee started working for the department, then instead of assigning it to the relationship we should assign it to the Employee entity. Assigning it to the employee entity makes sense as each employee can work for only single department but on the other hand one department can have many employees working under it and hence, it wouldn’t make sense if we assign Start_Date attribute to Department.
3. Many to many relationship:
In an organisation an employee can work on many projects simultaneously and each project can have many employees working on it. Hence, it’s a many to many relationship. So here assigning the Number_of_Working_hours to the employee will not work as the question will be that it will store which project’s working hours because a single employee can work on multiple projects. Similar the case with the project entity. Hence, we are forced to assign the Number_of_Working_hours attribute to the relationship.
Conclusion: Give attributes to a relationship only in the case of many to many relationship.