CODEWITHSUNDEEP
database-designentity-relationshipternary-operator
Cilenco
Cilenco

Reputation: 7137

Entity Relationship Model - Ternary operators

At the moment, I'm working a bit with databases. For this I created an entity relationship model but I have a small problem with reading it when it comes to ternary operators. Let's look at this example:

             -------------  (0|N)                    (0|N)  -------------
             |   Mother  |-----------------x----------------|   Father  |
             -------------                 |                -------------
                                           |
                                           |
                                           |
                                           |
                                           | (1|1)
                                     -------------
                                     |   Child   |
                                     -------------

This small entity relationship model reads like this:

A mother can have 1 to N childs - Okay

A father can have 1 to N childs - Okay

A child has exatcly 1 father and 1 mother - Okay

But it is also possible to say:

A father has N mothers - Not okay

A mother has N fathers - Not okay

So have I understood something wrong or are these ternary operators not obvious? With this implementation it is possible to create inconsistent datasets. Is the orientation important for these operators or do I have to read them in another way?

Upvotes: 2

Views: 1427

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Your primary entity is People. Mother, Father and Offspring are relationships between people.

create table People(
    ID    int primary key auto_generated,
    Sex   Char( 1 ) check( Sex in( 'F', 'M' )),
    ...
);
create unique index UQ_People_Parent on People( ID, Sex );

If ID is itself unique, why make the combination ID & Sex unique?

create table Parents(
    MotherID   int,
    FemaleFlag char( 1 ) check FemaleFlag = 'F',
    FatherID   int,
    MaleFlag   char( 1 ) check MaleFlag = 'M',
    constraint FK_Parent_Mother foreign key( MotherID, FemaleFlag )
        references People( ID, Sex ),
    constraint FK_Parent_Father foreign key( FatherID, MaleFlag )
        references People( ID, Sex ),
    constraint PK_Parents primary key( MotherID, FatherID )
);

This sets up an m-n intersection table. This is fine as a parent can have children with different spouses. The addition of the Flag allows us to enforce the restriction that Mothers must be a female Person and the Fathers must be a male Person.

create table OffSpring(
    PersonID   int primary key,
    MotherID   int,
    FatherID   int,
    constraint FK_OffSpring_Parents foreign key( MotherID, FatherID )
        references Parents( MotherID, FatherID ),
    constraint FK_Offspring_Person foreign key( PersonID )
        references People( ID )
);

An Offspring can be Daughter or Son so there is no distinction according to sex.

If we have the ID of a parent and we want to find their offspring, we can join People directly to OffSpring to find them, on the ID field to MotherID field or FatherID field as appropriate. If we have the ID of an OffSpring, we can join OffSpring directly to People on MotherID field or FatherID field to ID field. The Parents table is mostly just to enforce that 1) the mother and father have been defined as a pair and 2) mother must be female, father must be male.

For everyone reading this, please spare us any politically correct gender ambiguity nonsense. This is for illustration only. If anyone wants Parent1 and Parent2 instead of Mother and Father, with either or both being male or female, then design it like that.

Upvotes: 2

Joel Brown
Joel Brown

Reputation: 14408

Your example is not one of a ternary relationship.

Here is a more accurate ERD that fits your business rules correctly:

ERD

An example of a ternary relationship can be seen and is explained here. From that page:

A course offering must have at least one person, but may have many (at least two one would think: the instructor and a lonely student), and a person may be involved in some way with many courses, or none at all. The role attribute of the relationship indicates that person's role in the offering: student, instructor, etc.

Upvotes: 2

Related Questions