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.
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:
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).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.