2

Upon generating an entity-relationship diagram of my database I observed several occurrence like this:

enter image description here

This diagram (in IDEF1X notation) shows the following:

  1. Our team's DB convention is that each primary table (i.e., not an association-type table) has its own unique primary key called db_id.
  2. iam_projects.db_id is a foreign key in iam_project_rules (the project_id field), hence the connecting line.
  3. iam_projects.db_id is not part of the primary key of iam_project_rules (hence the dotted line style).
  4. A giveniam_projects.db_id may appear zero or more times in iam_project_rules (hence the solid circle on the right end of the connecting line).

Now, here's what my question is about. As I understand the notation, the diamond on the left end of the connecting line changes the left side from "one" to "zero or one". In words, that posits this claim to be true:

There may exist a row in iam_project_rules whose project_id does not appear in iam_projects.

But if iam_project_rules.project_id is a foreign key to iam_projects, which it is, then that claim must be false.

Where is the error in my analysis or assumptions?

3

According to this IDEF1X cheat sheet, the diamond shows a relationship of an optional iam_project with zero, one or many iam_project_rules.

The optional can only be on the iam_project side, since iam_project_rules is already "optional" (since there can be zero rules associated with an iam_project).

This is possible since the relation is between independent entities, i.e. both could exist without the other:

Independent Entities: An entity which does not depend upon any other entity in a model for its identification.
Dependent Entities: Depend both for their existence and their identification upon other entities in the model.

This means that:

There may exist a row in iam_project_rules whose project_id is null, which means that the rule is independent of any project.

If you want to have a non-optional relationship, you need to add a NOT NULL constraint on your foreign key.

Another alternative could be to align your data model with the IDEFX1 semantic and make iam_project_rule dependent on iam_project by including the foreign key project_id in its the primary key.

| improve this answer | |
  • Thank you for the response! I thought it might be a NULL issue of some sort, but every field in both tables is already defined with a NOT NULL constraint. If that is the only possibility, then perhaps the diagram generator has a bug (I am using dbeaver). Let me know if you have other thoughts. – Michael Sorens Oct 20 '19 at 1:47
  • @MichaelSorens yes, the generator must have some bugs, because whatever the notation, a non null foreign key clearly indicates that it’s not optional. So the diagram is not consistent with the semantic that we can deduce from the db constraints. – Christophe Oct 20 '19 at 7:17
  • Thanks for the confirmation. I posted a bug report on dbeaver so we will see what they have to say. – Michael Sorens Oct 20 '19 at 16:07

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.