Largely theoretical question or idea discussion

Lets say we have several simple dictionaries:

create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer primary key, description varchar(256));
create table dic3(code integer primary key, description varchar(256));

And we have a table which references these dictionaries

create table TabA (
   id integer primary key,
   d1 integer null references dic1(code) on delete set null on update cascade,
   d2 integer null references dic2(code) on delete set null on update cascade,
   d3 integer null references dic3(code) on delete set null on update cascade

Can we create user type so that a field of it will automatically be defined as foreign key? So if we have a second table:

create table TabB(
   id integer primary key,
   d1 ref_dic1,
   d2 ref_dic2,
   d3 ref_dic3,

And I want the TabB be the same as TabA.

I am not sure this can be done in SQL Server, but most likely we can write such extension for SQLite (maybe someone saw such extension and can share a link?)

When we select from such table, we can join with a dictionary to see a description rather then a code. But to filter we have to use code (or join with the dictionary).

select id, dic1.description as d1_name
from TabA
join dic1 on TabA.d1=dic1.code
join dic2 on TabA.d2=dic2.code
where dic2.description="Apple"

With UDDT it should be possible to write something like

select id, dictionary(d1) from TabB where dictionary(d2)="Apple"
// or even
select id, d1 from TabB where d2="Apple"
// with autocall of dictionary decoding, since d1 and d2 are UDDT

This can be done in SQL Server with functions even without UDDT. But it would require a bunch of functions like dictionary_dic1(@code), dictionary_dic2(@code), etc. One for each dictionary. Or pass the name of the dictionary table as parameter, and use dynamic sql - performance would suck a lot, but it is possible. With UDDT, I hope to avoid this and have just one dictionary() function which would automatically pick a correct dictionary table.

The SQLite extension on the other hand should handle such code with ease. Probably. Again, did anyone saw such extensions? link, please?

So, what do you think? Is there a value in this or do you see any hurdles which would make such feature impractical?

Did anyone saw SQLite extensions (even half baked) in this direction?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.