Hi Tom,
One of our applications has a basic 'customer' type schema: customer table, address, emails, orders, etc. At the heart of it is 'customer' itself defined with the following (all not null)
id int (pk),
customer_type char(1),
customer_unique_id char(10),
fk_inbound_feed_id int,
fk_contact_detail_id int,
fk_billing_agency_id int,
... [10 more FKs that define customer attributes]
A customer can be of 2 types: commercial or public. Because attributes of each are different, we've opted to keep each in a separate table: customer_commercial and customer_public. There is a 1:1 relationship between a record in 'customer' table and a given record in either of those tables--something we can't enforce, as 2-way FK would block inserts (unless you have advice on how to do this). We decided to keep 'customer' as main entity, so the other two tables are structured as:
id int (pk)
fk_customer_id int, (part-FK)
customer_type char(1), (part-FK)
... [individual attributes]
So customer_public (fk_customer_id, customer_type) is FK to customer(id, type).
We are not currently using the unique id as part of any key, just sequence-autogenerated IDs.
Previously the data was inserted by stored proc. For new records, it'd create contact info, billing, etc. records, then insert a new customer record, get its ID and then push the rest of the data down into either customer_public or customer_commercial.
There are several questions we've been stuck on with this:
1. Should we have gone the other way (i.e., inserting commercial/public records first and then rolling it up to 'customer')?
2. Should we be using the unique key instead as part of FK? (The key is unique globally, i.e., you could never have a public customer and commercial one with the same unique key)
3. How do we make sure that, after the code inserts the record into the first table ('customer' for the moment) that it also inserts corresponding records to the offshoots (comm/public)? In other words, is there a way to better enforce that 1:1 actually happens?
The reason for the 3rd question is that, in addition to feeds, some of this data is created in the GUI (at small 'corrective' volumes), and we've seen a few instances where the app code inserted into the 'customer' table but not into commercial/public. What can we do on the DB level to ensure this doesn't continue to happen?
Many thanks in advance--apologies if this is a bit roundabout...
1. As customer is the master entity I would start with this. But it comes down to how you design your foreign keys.
2. At the minimum you should have a unique constraint on customers.customer_unique_id!
What you need to consider is:
- Can you ever assign a new unique id to a customer? If so, you'll want to keep the surrogate key. This prevents the "cascading PK update" problem.
- How will you do lookups of customers and their associated tables?
Joining on long characters can be slower than integers. So this would favour sticking with the surrogate. But say people are searching for customer items (e.g. addresses, emails, ...) using the unique id. If you make this the primary key and use it in your child tables then you can avoid the join in the first place!
3. Oracle doesn't allow conditional FKs. i.e. one column which could point at one table or another based on some other value.
But you can enable this by creating extra columns on the parent. One per child table type, holding a copy of the primary key. The child tables can then have deferrable FKs pointing back to these. This guarantees the 1:1 relationship.
If you're on 11g you can do it with a combination of virtual columns and deferrable FKs. The process is:
- Create a virtual column for each customer type. This returns the customer_id if the row is of that type. It's null otherwise
- Create a unique constraint on each of these virtual columns
- Create a deferrable foreign key on the child tables back to the appropriate virtual column in the parent.
Here's an example with just public customers for brevity:
create table customers (
customer_id int not null primary key,
customer_type varchar2(10) not null,
public_customer_id int as (
case when customer_type = 'PUBLIC' then customer_id end
),
unique (public_customer_id, customer_type)
);
create table public_customers (
customer_id int not null primary key,
customer_type varchar2(10) not null
check (customer_type = 'PUBLIC'),
foreign key (customer_id, customer_type)
references customers (public_customer_id, customer_type),
unique (customer_id, customer_type)
);
alter table customers add constraint fk
foreign key (public_customer_id)
references public_customers (customer_id)
deferrable initially deferred;
insert into customers (customer_id, customer_type) values (1, 'PUBLIC');
commit;
SQL Error: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found
02091. 00000 - "transaction rolled back"
insert into customers (customer_id, customer_type) values (2, 'PUBLIC');
insert into public_customers values (2, 'PUBLIC');
commit;
insert into customers (customer_id, customer_type) values (3, 'COMM');
commit;
You've said your on 10g. You can still do this. But the type_id columns in customers will have to be real columns. This makes it more convoluted and require more storage. But still possible:
alter table customers drop constraint fk ;
drop table public_customers purge;
drop table customers purge;
create table customers (
customer_id int not null primary key,
customer_type varchar2(10) not null,
public_customer_id int,
check (
( customer_type = 'PUBLIC' and public_customer_id is not null and public_customer_id = customer_id ) or
( customer_type <> 'PUBLIC' and public_customer_id is null )
),
unique (public_customer_id, customer_type)
);
create table public_customers (
customer_id int not null primary key,
customer_type varchar2(10) not null
check (customer_type = 'PUBLIC'),
foreign key (customer_id, customer_type)
references customers (public_customer_id, customer_type),
unique (customer_id, customer_type)
);
alter table customers add constraint fk
foreign key (public_customer_id)
references public_customers (customer_id)
deferrable initially deferred;
insert into customers (customer_id, customer_type) values (1, 'PUBLIC');
commit;
SQL Error: ORA-02290: check constraint (CHRIS.SYS_C005191) violated
insert into customers (customer_id, customer_type, public_customer_id) values (2, 'PUBLIC', 2);
commit;
SQL Error: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found
02091. 00000 - "transaction rolled back"
insert into customers (customer_id, customer_type, public_customer_id) values (3, 'PUBLIC', 3);
insert into public_customers values (3, 'PUBLIC');
commit;
insert into customers (customer_id, customer_type) values (4, 'COMM');
commit;