Which is the child table in a Identifying or Non-I

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

There is no strict rule that will determine the role of a table in a relationship. In fact, that's the beauty and innovation of the relational model: no hierarchies.

Usually, if there is a hard dependency from certain table to another, the child or parent role are determined by semantics of the tables. Example: in a order, order_details relations, it's pretty obvious that order is the parent and order_details is the child.

In other cases, it's not clear what role a relation plays in a relationship. Example: orders and customers relation. If you perform a query to get all orders belonging to a certain customer then the parent might be customers and orders are children. But you can also do a query to get all shipment information (stored in customers relation) for a specific order, and in this case, you might argue that order is the parent while customers is the child in this query.

As I said before, when relational model was invented in late 70's, one of its main benefits over one of the reigning paradigm (hierarchical data model) was the ability of look for related data regardless of their dependency.

Answer 2

A great definition is proposed here:

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table. (...) Formally, the "right" way to do this is to make the foreign key [i.e. the parent's primary key] part of the child's primary key.

Answer 3

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.