On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted <dollien@gmail.com> wrote:
Could you perhaps elaborate on how a surrogate key allows one to insert garbage into the table? I'm afraid I don't quite get what you're saying.
A bit contrived but it makes the point:
Company:
C1 (id c1)
C2 (id c2)
Department:
C1-D1 (id d1)
C1-D2 (id d2)
C2-D1 (id d3)
C2-D2 (id d4)
Employee:
C1-E1 (id e1)
C1-E2 (id e2)
C2-E1 (id e3)
C2-E2 (id e4)
Employee-Department:
e1-d1
e2-d2
e3-d2
e4-d4
The pair e3-d2 is invalid because e3 belongs to company c2 while d2 belongs to company c1 - but we've hidden the knowledge of c# behind the surrogate key and now we can insert garbage into employee-department.
David J.
This seems like hierarchical data, where employee's parent should be department, and department's parent is company. So it wouldn't be possible to "insert garbage" since Company is not stored in the Employee table, only a reference to Department (and Company determined via Department). Isn't that how normal hierarchical data works?