Use default parameter/setting (row) if not set specific for entity

The other day I was asked about the following:

We have a table VARIABLE and Variable entity. A new requirement is to restrict availability of a limited number of these entities to chosen customers. The volumes? Quite low – now around 70 rows, it might slightly exceed 100 in the future. No LOBs.

So far we have been doing such filtering by applying conditions on FK columns referencing COMPANY table. We want to do the same here. But there’s a small difference: a Variable can be either visible to all customers (no restrictions) or to 1 or more chosen customers. So, an intersection table is quite a natural choice. E.g. COMPANY_VARIABLE table with 2 columns only: FK_VARIABLE, FK_COMPANY. Only FK_VARIABLE would be not null. So, the FK referencing COMPANY table would be optional.

I’d like to avoid keeping many rows in the new table for a variable without visibility restrictions. For such variables it would be enough to have only 1 row with null in FK_COMPANY. Otherwise, it would be

  • too cumbersome,
  • error-prone (when adding a new company or a no-restriction variable, you’d always have to add many rows to the intersection table)
  • and taking DB storage unnecessarily.

In turn, if a variable were to be restricted, we would add a new (VARIABLE_ID, COMPANY_ID) row to the table.

My first thought was: “You can not do this, because you need a composite primary key on the two columns FK_VARIABLE and FK_COMPANY in the COMPANY_VARIABLE table. And a column which is a part of the primary key will per definition be NOT NULL”. Of course you can introduce a surrogate key and use this as a primary key, and then create a unique index on the two columns. Then you are aloud to define the FK_COMPANY as nullable.

But could I do the same thing without a surrogate key? Well I thought of one solution using an invisible virtual column. I’m not saying that this is a good solution. Then I would have to test it a little more first (Note! If you have some experience with something similar, please write a comment to this blog post).

So here is the idea:

-- Table: CUSTOMER
create table customer (id number);
alter table customer add constraint customer_pk primary key  (id);
insert into customer values ('1001');
insert into customer values ('1002');

-- Table: VARIABLE
create table variable (id number, variable varchar2(30));
alter table variable add constraint variable_pk primary key  (id);
insert into variable values (1, 'Test');
insert into variable values (2, 'Test 2');
insert into variable values (3, 'Test 3');

create table customer_variable (fk_customer number, fk_variable number );
alter table customer_variable add (fk_customer_all number as (case when fk_customer is null then -1 else fk_customer end ));
alter table customer_variable add (
  constraint customer_variable_pk primary key (fk_customer_all, fk_variable),
  constraint custvar_customer_fk foreign key (fk_customer) references customer (id),
  constraint custvar_variable_fk foreign key (fk_variable) references variable (id)
alter table customer_variable modify (fk_customer_all invisible);
insert into customer_variable values (null, 1);
insert into customer_variable values (null, 2);
insert into customer_variable values (1001, 1);
insert into customer_variable values (1002, 2);
insert into customer_variable values (1002, 3);
-- These should fail (constraint violations)
insert into customer_variable values (null, 1);
insert into customer_variable values (1002, 2);
-- find all valid variables for a customer
with my_variable as (
   select cv.*, rank() over (partition by fk_variable order by fk_customer nulls last) rn 
   from customer_variable cv 
   where fk_customer=:b1 or fk_customer is null
select fk_variable
from my_variable
where rn=1;


Post a Comment

Your email is never published nor shared. Required fields are marked *