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_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?