Database design/ ERD diagram verification

Question

I would be grateful if you could take a look to my ERD diagram which is related to description:

Database contains persons records (since 1900). Each person can have mother and/or father. Women can have one husband and men can have one wife. Persons work in companies, which contain name and one chairman. One person can work in one or more companies, employe on mandatory contract or employment contract.

and the example query is: find person (name and surname) which have largest numbers of grandchildrens.

and below is my ERD diagram but i'm not sure about the mother/father and wife/husband parts.

Thanks in advance

Answer 1

Your "ERD", while commonly called such, is really a table diagram. A proper ER diagram has to be able to represent an ER model, meaning it should be able to represent ternary and higher relationships among other things. I recommend using Chen's notation for ER diagrams. That doesn't mean I'm against table diagrams - they're just used for different purposes. ER diagrams for conceptual models, table diagrams for physical models. In-between those are relational diagrams for logical models.

Your diagram also doesn't implement the given description very well. Consider "Each person can have a mother and/or father". Your relationship table allows a person to be associated with a number of relationship_types, but it doesn't associate two persons in parent and child roles. A better approach might be to have father and mother tables with parent_id and child_id fields that refer to person_id, or a single parent table with parent_id, child_id, relationship_type fields.

For married people, you could have a table with husband_id, wife_id fields. As with father and mother tables, this doesn't ensure that only persons of the matching gender is recorded in each role. It's possible to enforce this in both cases by creating disjoint subtype tables for genders of person. However, this isn't commonly done (and these days, flexibility in your data model is more likely to be seen as an advantage than a problem), and I suspect it may be overkill for your project.

Employment is better modeled. The main issue I see there is the one-to-one association between agreement_type and employment_condition. I assume agreement_type is meant to record mandatory contract and employment contract. However, with a one-to-one association, you can't have two or more employees with a mandatory contract. I believe each agreement_type should be usable in multiple employment_conditions.

I'm also concerned with the key(s) of employment_condition. condition_id and agreement_name are both indicated as foreign keys (orange) and part of the primary key (key icon). What does condition_id refer to, and why are they included in the PK? I would expect the combination of person_id, company_id to be enough to identify a many-to-many relationship between companies and employees.

Similarly, person_id in company is included in the PK. Based on the given description, I expected that company_id would determine both the name and the chairperson (person_id). BTW, I suggest you change the name of that field to chairperson_id to be more descriptive. Imagine if we had more persons in individual roles - we couldn't call each field person_id, right? It's generally more useful to name fields based on their role rather than their domain, though the two often coincide.