Schema Design Using Surrogate Key
448221Jun 4 2006 — edited Jun 6 2006I have 2 tables using surrogate keys and a denormalized d_name column to avoid join when fetching d_name:
create table departments (
pk int primary key,
d_id int unique, /*the real pk*/
d_name varchar2(30)
);
create table employees (
pk int primary key,
e_id int unique, /* the real pk*/
/* e_ pk int references departments(pk)*/
d_id references departments(d_id),
d_name varchar2(30)
);
My problem is should we use d_id as the FK in employees or add and use the surrogate key (pk)? What are the pros and cons of each approach?
I would add triggers to both tables so that the d_name,d_id could be changed in any one of them and values auto populated to the other. If I use d_id as the FK, would the constraint causes error before I update the other table. e.g. I change some departments's d_id to 10, then I would need to change that rows to 10 in employees. But before my trigger change the row, would integrity checking fails?