Search code examples
relationshiperd

Do Entity-Relationship diagrams need attributes to represent relationships?


I'm designing an ERD for an assignment. The brief will contain relationships, such as "Every employee is assigned to a single department".

I could add a field in the entity: "Assigned Department". But with what I've seen so far across sources, this is hit or miss. Some sources do this. Some prefer to leave it out, instead adding a textual label "assigned to" to the line connecting the two entities.

Is there any guidance on this? I'm going to normalise this later, so if this is a normalisation step rather than an ERD step I'd like to preferably do it when normalising.


Solution

  • You can provide information as an attribute (field), for example Name of an Employee or Id of a Department.

    But in ERD, this would mean that:

    • It's just a value like another.
    • In the case of the assigned department, you'd miss the entity Department and could not represent different relationships (e.g. if you'd have to go for a many to many relationship with employees working part time over several departments).
    • You would miss the identity of the department: if a department would get renamed, you'd have to find back all the attributes in all the entities where a department could have been used, and update the name if it matches the old value. With a proper entity, you'd just have to rename an entity, and all the related entities would still relate to it via the primary key.
    • It would be difficult to navigate the relationship. E.g. if every employee is assigned to one entity, you could likewise have one employee managing the department, but it would be less straight forward to realise how to navigate between entities to find the boss of an employee.

    As a hint, as soon as a relationship is expressed, with cardinality constraints or mandatory vs. optional participation, it'd be safer to consider making it an entity.

    Edit: the attribute in addition to the entity?

    If you anyway have the Department entity and the relationship, the question is whether your ERD is a conceptual model documenting your domain, or if it is a physical model documenting your implementation.

    Physical models show all the tables with all the fields required to implement the relationships. The relationships are then only visual aids.

    Conceptual models focus on the domain's entities and their relationships, independently of the db implementation. Here, you should better not add the attribute:

    • you could add an attribute that correspond to the primary key of related attribute.

    • such an attribute would however be redundant with the relationship and create ambiguity: is it the implementation of the relationship or is it another relationship on top? If the ERD would be used with a DB generator, the attribute might well be duplicated

    • moreover, to be consistent, you would have to add it everywhere. But then you would have to transform the many-to-many relationships into fictuous entities corresponding to join-tables. You'd also have to complement attributes of weak entities, etc... in short, you'd get to a physical model.