Identifying relationship - many to many

Question

I have made a small database for a daycare centre. I have been reading up on identifying and non-identifying relationships and am still a bit confused about the whole thing. I have been using MySQL Workbench to design the database.

Now, the criterias for parents and children are that a Parent can have one or more Children and vice versa - ergo, the relationship between Parents and Children is a many-to-many. The best praktice to solve this (as I understand) is to make a third table - Parets_Children and use that as a "connection" between the other two:

Parents - 1:n - Parents_Children n:1 - Children

Now the issue I have is wether or not to use an identifying relationship here. The logic is that no child can exist without a parent and the parents wouldn't be members of the daycare unless they had at least one child there. Neither of them would exist "alone". However, when letting MysQL WB sorting it out for me, it creates a non-identifying relationship (I think).
Very simplified code:

CREATE TABLE 'parents' (
  'id_parents' SMALLINT UNSIGNED NOT NULL ,
  'name' VARCHAR(48) NOT NULL ,
  PRIMARY KEY ('id_parents');

CREATE TABLE 'parents_children' (
  'id_parents' SMALLINT UNSIGNED NOT NULL ,
  'id_children' SMALLINT UNSIGNED NOT NULL ,
  PRIMARY KEY ('id_parents', 'id_children') ,
  FOREIGN KEY ('id_parents') REFERENCES 'parents' ('id_parents' )
  FOREIGN KEY ('id_children' ) REFERENCES 'children' ('id_children' );

CREATE TABLE 'children' (
  'id_children' SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  'name' VARCHAR(48) NOT NULL ,
  PRIMARY KEY ('id_children');

Would this output be sufficient to reach the desired function? Have I misunderstood the whole relationship thing? I hope my question isn't too obsqure.

score 2 · Accepted Answer · answered Sep 12 '12 at 09:11

2

From the schema you provided I can see that the Foreign Key Constraint exists on table parents_children, which will ensure that the link between parent and child can only exist if both parent and child exists.

This does not however stop you from inserting an entry into parents, without an accompanying entry in parents_children or for that matter children.

So in short, this schema allows for a parent to exist, without a child.

The problem with your logic here would be that both the parent and child needs to be created before the relationship can be created in parents_children (due to the foreign key relationships). However, the additional logic that you are looking for (no child no parent and vice versa) would require all a link between parent and child before a parent or child can be created.

You see the chicken/egg problem here?

answered Sep 12 '12 at 09:11

Adriaan Stander

162,879
31
289
284

I see, however, this can't be the first time in history where a similar issue appears. Is there some "best practice" to solve it or should I just go by and code the restrictions in PHP instead? – Sandokan Sep 12 '12 at 09:49
I would have created a validation report, that allows the user to see which parents/children exist without a link, and handle the cleanup from there. Haev you given any thought to how you would handle a child in the fostercare system? No known parent there. X-) – Adriaan Stander Sep 12 '12 at 09:56
Sounds like an idéa. And as for fostercare, this is just a normal daycare centre, not a fosterhome, where every child has at least one adult responsible for it, be it biological or foster parent. – Sandokan Sep 12 '12 at 10:56
@Sandokan _"Is there some "best practice" to solve it or should I just go by and code the restrictions in PHP instead?"_ This could be enforced declaratively in the database itself, using deferred foreign keys, but unfortunately MySQL doesn't support them. The idea is to have one more foreign key where parent _also_ references the link table (in addition to link referencing parent), forcing at least one link row to exist, but you'd need to _defer_ the enforcement of FKs after the parent and child and link rows have all been inserted, to break the chicken-and-egg problem. – Branko Dimitrijevic Sep 12 '12 at 11:51

score 1 · Answer 2 · answered Sep 12 '12 at 09:41

The logic is that no child can exist without a parent and the parents wouldn't be members of the daycare unless they had at least one child there.

To implement this is in a simple way.. have one table MyDayCare where you can have the columns like: RecordID, ChildID, ChildName, ParentID, ParentName
While inserting records you will have to calculate the next ChildID and ParentID. You can have other coulms in your table like DateOfBirth so as to uniquely identify each parent-child pair.

When you delete a child, the parent would also be deleted and vice-versa.
You can even store records of more than one parent for the same child (in different rows). By using simple SELF JOINs you can fetch any data you want for a particular child or parent.

Please note that this is not the best way of designing database tables, but as per your requirement this can also work.

Actually, I would like the Parent(s) to be deleted when their last child is deleted, but I get your drift. — Sandokan, Sep 12 '12 at 09:47
I can not think of any way to implement logics like "the Parent(s) to be deleted when their last child is deleted" just by adopting a particular table structure or just by establishing some relationships. For that you will have to implement that logic in some stored proc (used for deleting a child/parent) or use triggers. Or as astander suggested you will have to manage it asynchronously by running some reconcilation job or reports. — Saurabh R S, Sep 12 '12 at 10:09

score 1 · Answer 3 · edited May 23 '17 at 11:44

Whether to use identifying relationships or not on the junction (aka. link) table Parets_Children depends on what kind of primary key you wish this table to have.

Identifying relationships will produce a "natural" composite PK {id_parents, id_children}, as you already noted. Non-identifying relationships would allow you to have a "surrogate" PK, in addition to (now alternate) key {id_parents, id_children}.

Unless you have a specific reason for a surrogate key, having just a natural PK on the junction table should be quite enough.

The logic is that no child can exist without a parent and the parents wouldn't be members of the daycare unless they had at least one child there.

Unfortunately, the presence of at least one child would not be easy to enforce through purely declarative means. In fact (to my knowledge at least) it is impossible to declaratively enforce it on a DBMS such as MySQL that doesn't support deferred constraints, which would be necessary to break chicken-and-egg problem when inserting parent and child together.

If it did (support deferred FKs), the presence of at least one child per parent could be enforced declaratively like this:

enter image description here

The chicken-and-egg problem caused by the circular dependency between parent and parent_child would be resolved by deferring the enforcement of FKs until after all the necessary rows have been inserted.

As it stands, you'll have to either do it in the application code, or make the stored procedures that ensure the correct behavior and then manipulate the data only through them.

Thanks. I'm going for a simpler approach now that you made me abit wiser and enforce said restrictions in the code instead. — Sandokan, Sep 12 '12 at 13:37

Identifying relationship - many to many

3 Answers3

Linked