You make a good point that you can avoid some number of joined queries by using what's called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application.
That is, you can measure the queries in your application that are the most important to be speedy, because they work with large volumes of data or they are executed very frequently. If these queries benefit from eliminating a join, and do not suffer by using a varchar primary key, then do it.
Don't use either strategy for all tables in your database. It's likely that in some cases, a natural key is better, but in other cases a surrogate key is better.
Other folks make a good point that it's rare in practice for a natural key to never change or have duplicates, so surrogate keys are usually worthwhile.
Assuming that I have understood your scenario correctly, this is what I would call the right way to do this:
Start from a higher-level description of your database! You have employees, and employees can be "ce" employees and "sn" employees (whatever those are). In object-oriented terms, there is a class "employee", with two sub-classes called "ce employee" and "sn employee".
Then you translate this higher-level description to three tables: employees
, employees_ce
and employees_sn
:
employees(id, name)
employees_ce(id, ce-specific stuff)
employees_sn(id, sn-specific stuff)
Since all employees are employees (duh!), every employee will have a row in the employees
table. "ce" employees also have a row in the employees_ce
table, and "sn" employees also have a row in the employees_sn
table. employees_ce.id
is a foreign key to employees.id
, just as employees_sn.id
is.
To refer to an employee of any kind (ce or sn), refer to the employees
table. That is, the foreign key you had trouble with should refer to that table!
Best Answer
You can simply do