Search code examples
databasedatabase-designentity-relationshipdatabase-schemaerd

In an ERD can 2 actions be connected to each other?

My program allows the user to select up to 3 suburbs in their local region. The website emails the user about road crashes that occurred in those suburbs. It stores their UserID and the suburbs they selected in table UserSuburbSelections.

My ERD connects 2 actions, selects and stored in:

enter image description here

Am I allowed to do that? Is it good practice?

Solution

  • Whatever your program performs as actions, in an ERD it's about entities and relations. Here:

    • User or UserDetail is clearly an entity
    • Suburb is an entity as well.
    • The UserSuburbSelection should better be a either a simple relationship between User and Suburb, or an associative entity, if for example the relation should have it's own attributes, such as the order of preference.
    • RoadCrashes would probably a separate entity describing a crash, that would then be related to a suburb.

    But your ERD must chose use a notation consistently:

    • Chen's notation, that represents relations with a diamond, and associative entities as a diamond boxed in a rectangle. It is forbidden to relate two relationships directly between them.
    • Crow's foot notation, that represents relationships with a simple line, adorned with some symbols at each end to express multiplicities. Associative entities are represent as an intermediary entity.

    Your ERD mixes both notations, making whatever you call "action" a relationship according to Chen, but using Crow's foot cardinality and participation notation between entities and the relationship, which is confusing. Moreover, you've modelled a direct link between two relationships, which is not possible in ERD (unless one if it is an associative entity).