1

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:

ER - Standards and Controls Model

Is this a correct conceptual schema for such a data model?

  • So a control may be shared among multiple standards? – Max Vernon Aug 17 '17 at 2:58
  • No, apologies, I should have defined this. The arrow denotes that a Control may only be associated with at most one Standard. – Alistair Gillespie Aug 17 '17 at 3:07
1

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.