-2

My scenario is that I have a super type named PERSON and two sub types EMPLOYEE and CUSTOMER. I use the Primary key from PERSON, PersonID, in both EMPLOYEE and CUSTOMER as foreign keys. However, I don't use that as my primary key in EMPLOYEE and CUSTOMER but rather I make a new surrogate primary keys named EmployeeNum and CustomerNum in their respective tables. I was wondering does it break some sort of well formed/normalization rule if I do this? For example, in EMPLOYEE I think its clear that personID-->(employeeid, hiredate, hourlywage) and also employeeid-->(personid, hiredate, hourlywage), essentially both these keys can uniquely identify a unique row by themselves. Is that OK in practice? (also assume I don't want a composite primary key).

  • 4
    It's ok regarding normalization but what benefit do you gain by adding a second surrogate key in these tables? Why can't you use personID and completely remove employeeID and `customerID? – ypercubeᵀᴹ Mar 1 '15 at 20:13
6

Since both PersonID and EmployeeID are candidate keys (and presumably will be implemented with uniqueness constraints) your proposed table design satisfies Boyce Codd Normal Form and therefore 3rd Normal Form as well. That doesn't necessarily make it a good idea. In principle, it's no bad thing to provide alternative identifiers for things but in this case I don't see the logic for doing so.

You appear to be saying that PersonID and EmployeeID are both "surrogate" keys but in that case what natural key will you be using in the business domain? I guess at least one of those is actually the domain key ("natural" key) for employees, but then what is the other one for? How is the use of an additional key supposed to enhance data integrity or usability?

Surrogate keys imply certain overheads: extra indexes; additional lookups and joins when performing data access and manipulation; more code complexity. The decision to use a surrogate ought to be taken with due care and consideration of the advantages and disadvantages. It should not be a default assumption that every table needs yet another surrogate.

-2

This approach does break a normalization rule in that employeeid and customerid are functionally dependent on personid. A database such as you describe could not be said to be in the Third normal form for this reason.

Further expansion for discussion based on answer feedback:

Let's assume that the tables under discussion take the following form:

Person
PersonID (PK)| {Person Attributes}

Employee
EmployeeID(PK)| PersonID (FK)| {Employee Attributes}

Customer
CustomerID(PK)| PersonID (FK)| {Customer Attributes}

In this case, we have two keys for each row in both the Employee and Customer tables. In addition, we have a functional dependency for each table between a non-primary key and a primary key, namely:

PersonID --> CustomerID
PersonID --> EmployeeID

This clearly violates the normal form, as we additionally have transitive functional dependencies of a non-primary key column:

PersonID --> CustomerID --> {Customer Attributes}
PersonID --> EmployeeID --> {Employee Attributes}

Additionally, even with PersonID as the primary key on the table, we retain transitive functional dependencies that are not permissible under 3NF.

This arrangement:

Person
PersonID (PK)| PersonName

Employee
PersonID (PK)| EmployeeID | {Employee Attributes}

Customer
PersonID (PK)| CustomerID | {Customer Attributes}

Retain the functional dependencies:

PersonID --> CustomerID --> {Customer Attributes}
PersonID --> EmployeeID --> {Employee Attributes}

Decomposing this, we remove customer and employee to their own tables. Depending on the business rules, i.e. whether a person can have one or more "personType", the proper design is either:

For 1:1 person : persontype

Person
PersonID (PK)| PersonType (Employee/Customer) | {Person Attributes}

Employee
PersonID (PK)| {Employee Attributes}

Customer
PersonID (PK)| {Customer Attributes}

For 1:Many person : persontype

Person
PersonID (PK)| {Person Attributes}

Employee
PersonID (PK)| {Employee Attributes}

Customer
PersonID (PK)| {Customer Attributes}

References:

3rd Normal Form Definition

Wikipedia - Attention section entitled "Nothing but the Key"

3NF Definition concentrating on functional transitive dependencies

  • 3
    Why isn't it in 3NF? I don't say that adding a second surrogate key in these two tables has any benefit, just that it doesn't break any normalization rule. – ypercubeᵀᴹ Mar 1 '15 at 20:07
  • 3
    PersonID and EmployeeID are both keys in Employee. On that basis there is no violation of 3NF. – nvogel Mar 2 '15 at 7:08
  • 4
    There's also this arrangement: EmployeeID(PK)| PersonID (UQ) (FK)| {Employee Attributes} where both EmployeeID and PersonID have unique constraints. That's what I (and sqlvogel) had in mind. – ypercubeᵀᴹ Mar 2 '15 at 15:03
  • 4
    Thomas, My point (and ypercube's) is that PersonID --> EmployeeID --> {Employee Attributes} is NOT a transitive dependency if EmployeeID is a key as well as PersonID. 3NF requires no distinction between primary and "non-primary" candidate keys. All candidate keys are equally important in 3NF as the Wikipedia link you posted makes very clear (I'm not advocating Wikipedia as a generally reliable source of information but in this instance it happens to be more accurate than the other links you referred to). – nvogel Mar 2 '15 at 15:17
  • 3
    Makes sense- agreed. The answer that sqlvogel posted is correct. – Thomas Cleberg Mar 2 '15 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.