I am designing a simple database as follows:
- The database should contains an evolving set of standards;
- each standard has a library of controls;
- A single control in each standard may map to one or more controls in another standard;
- A control may only belong to one standard. However, a standard may have many controls;
- A control must belong to a standard.
A rough sketch of the entity-relationship diagram (ERD) I have come up with is as follows:
Is this a correct conceptual schema for such a data model?
Best Answer
Using the "crow's foot" notation that @MDCCL has mentioned, the OPTIONALITY can be expressed by using solid and broken lines, respectively (solid -> MUST/mandatory, broken -> MAY/optional). E.g. one of your business rules is: "A control must belong to a standard." (5th bullet point of your question), then the line (relationship) going from CONTROL to STANDARD starts solid (notice that the - same - relationship STANDARD -> CONTROL starts with a broken line).
Also, a relationship should "work both ways (!)". The crow's foot notation allows us to label both ends of a relationship, e.g. we if it is correct to say that a STANDARD is constrained by one or more CONTROLs and each CONTROL constrains a STANDARD, then the first version of our ERD could look like the one below (the label text "has" is a bit vague ... try to be more specific/descriptive).
The "crow's foot" notation also shows the CARDINALITY very clearly e.g. you have modelled the second business rule "A single control in each standard may map to one or more controls in another standard", as 2 single solid lines, labelled "mapsTo" (one to one?, mandatory?). Using crow's foot notation, the fact that there is an optional one-to-many relationship between CONTROLs can be drawn like in the ERD below. (NOTE: I'm not claiming that the relationship labels I have used are correct for your scenario. It's just to show you that e.g. "has" is too vague. We know that the relationship "has" or "is", otherwise it would not exist in the model.)
Some people recommend using singular nouns (STANDARD, CONTROL instead of STANDARDS and CONTROLS) in ERDs. Maybe this makes finding the cardinalities easier.