Which is the child table in a Identifying or Non-Identifying Relationship?

Question

In the context of identifying and non-identifying relationships between tables, MySQL's documentation refers a lot to the tables as parent and child tables.

How do you determine which table is the parent table and which table is the child table?

Answer 1

A child table (A.K.A. weak entity) is a table whose primary key attributes depend on another table, thus the child table is identified or partially identified by rows in the table it depends on (parent). Rows in a child table cannot exist without a corresponding row in its parent table.

To illustrate, let's take a simple and completely relevant example we are all familiar with: Parents and children in the context of family. We can model out this relationship with tables like so:

In the model above, each row in the Parents table is uniquely identified by an SSN. The SSN is an intrinsic and unique attribute to each parent, thus it is a standalone or "strong" entity because it does not rely on another table to define its identity.

Children however, require a parent in order to exist (Parent_SSN must reference to an existing SSN in the Parents table).

Notice the composite primary key (Parent_SSN, Name) in the Children table. This means that children are uniquely identified by the combination of Parent_SSN and Name. You cannot query for an individual child based only on the Name field because multiple parents may have children with the same name. Likewise, you cannot query for an individual child based only on the Parent_SSN field because one parent may have many children. Taking that into consideration, children are partially identified by their parent, hence identifying relationship.

But can't children be uniquely identified by an SSN as well? Why yes, certainly. Let's go ahead and adjust our model to include that:

In this version of the model, notice we have introduced the SSN field for Children. The unique identity of children is now defined by their own intrinsic and unique SSN. Their identity no longer depends on the Parents table. Although the Parent_SSN field still references the SSN of the Parents table, it has no part in the unique identity of the child, thus parents have a non-identifying relationship to their children, and both tables can now be considered "strong" standalone entities.

As an aside, this version of the model has a few advantages over the first:

• One parent may now have two or more children with the same name, whereas the entity integrity constraint in the previous model would not allow for this.
• You can allow the Parent_SSN field to contain NULL to account for the event that you have data about the child, but do not know who his/her parent is.

In both of the above models, the Parents table is considered to be the parent table of the Children table. However, in non-identifying relationships like in the second model, Parents is only a parent table in the context of the foreign key Parent_SSN because Parent_SSN references/depends on SSN in the Parents table, but does not have any part in defining the actual identity of children.

To illustrate why context is important when deciding which tables are parent/child tables, consider the following example involving a circular dependency:

In this example, employees and departments are uniquely identified by their own attributes and do not derive any part of their identity from other tables.

Here, we have two non-identifying relationships: an employee works for a department (DeptNo in the Employee table), and a department is managed by an employee (ManagerSSN in the Department table). Which one is the parent table? ...Child table?

It depends on context — which foreign key relationship are you talking about? The Department table would be considered the parent table in the context of DeptNo in the Employee table because DeptNo is referencing/dependent on the Department table.

However, the Employee table would be considered the parent table in the context of ManagerSSN in the Department table because ManagerSSN is referencing/dependent on the Employee table.