What does Identifying really mean?

An identifying relationship (solid line) means the parent's primary key is part of the child's primary key. A non-identifying relationship (dotted line) means the parent's primary key is just a foreign key in the child table.

So what?

How are social security numbers defined? There doesn't really appear to be any logical structure to them. They just serve like license plates on cars. Every person has their on unique SSN just like every car has its own unique tag. My SSN is 987-65-4321. My dad's SSN is 123-45-6789. My SSN has absolutely nothing to do with his. This is a non-identifying relationship.

What if the government used a different method for tagging people? What if the SSN of the child was defined as their biological father's SSN followed by a sequence number? My dad had four kids. I'm the baby of the bunch, so my SSN would be 123-45-6789-4. If he had another kid after me, then it would be tagged 123-45-6789-5. This would have worked. It looks a little funny, but it has a nice side benefit of describing each person's paternal family tree. The obvious weakness of this type of SSN is that it gets awkward after a few generations. I have two children. Their SSNs would be 123-45-6789-4-1 and 123-45-6789-4-2. If my second child was male (she's not) and he had three children, then their SSNs would be 123-45-6789-4-2-1, 123-45-6789-4-2-2 and 123-45-6789-4-2-3. Their SSNs show four generations of their paternal family tree back to their great grandfather (my dad). This is an example of an identifying relationship. This is really cool... if you are a genealogist. It is just crazy for the rest of us.

As long as we're re-inventing the tagging system used by the Social Security Administration, let's also take into account the mothers as well as the fathers. The New and Improved SSN of the child will be defined as the father's SSN followed by the mother's SSN followed by a sequence number. My parents had four kids together. My dad's SSN is 123-45-6789 and my mom's SSN is 024-68-1357. I'm the fourth of their kids, so my SSN would be 123-45-6789-024-68-1357-4. My father-in-law's SSN was 987-65-4321 and my mother-in-law's is 975-31-8642. My wife is their third kid, so her SSN would be 987-65-4321-975-31-8642-3. My wife and I have two kids. Our oldest daughter's SSN would be 123-45-6789-024-68-1357-4-987-65-4321-975-31-8642-3-1. That's forty digits! Clearly, this method isn't feasible after a couple of generations. (But, it sure would have made the genealogy buffs happy since it contains so much embedded family tree information.)


Does any of this matter to us? No, not really. Most of the time, the relationships we create between our tables will be non-identifying. That is, the primary keys of the two tables will be independent of each other. Occasionally, however, we will create a table (or a bridge table) that makes use of identifying relationships to its parent (or parents). No big deal.

More FAQs