database - how do I model subtyping in a relational schema? -

is following db-schema ok?

• request-table

request-id | type | meta-1 | meta-2 |

this table stores requests each of has unique request-id. type either a, b or c. tell table contains specific request parameters. other have tables respective types. these tables store parameters respective requests. meta-1 additional info timestamps , stuff.

• type-a-table

request-id | param_x | param_y | param_z

• type-b-table

request-id | param_i | param_j

• type-c-table

request-id | param_l | param_m | param_n | param_o | param_p | param_q

the request-id foreign key request-table.

---

is design normal/best-practice? or there better/smarter way? alternatives?

it somehow feels strange me, having query on request-table find out type-table contains information need, actual query i'm interested in.

for instance imagine method given id should retrieve parameters. method need 2 db-access. - find correct table query - query table parameters

note: in reality have 10 types of requests, i.e. 10 type tables. there many entries in each of tables.

meta-note: find hard come proper title question (one not overly broad). please feel free make suggestions or edit title.

for exclusive types, need make sure rows in 1 type table can't reference rows in other type table.

create table requests (   request_id integer primary key,   request_type char(1) not null      -- use table constrain valid types.     check (request_type in ('a', 'b', 'c', 'd')),   meta_1 char(1) not null,   meta_2 char(1) not null,   -- foreign key constraints don't reference request_id alone. if    -- did, might reference wrong type.    unique (request_id, request_type) ); 

you need apparently redundant unique constraint pair of columns can target of foreign key constraint.

create table type_a (   request_id integer not null,   request_type char(1) not null default 'a'     check (request_type = 'a'),   primary key (request_id),   foreign key (request_id, request_type)      references requests (request_id, request_type) on delete cascade,   param_x char(1) not null,   param_y char(1) not null,   param_z char(1) not null ); 

the check() constraint guarantees 'a' can stored in request_type column. foreign key constraint guarantees each row reference 'a' row in table "requests". other type tables similar.

create table type_b (   request_id integer not null,   request_type char(1) not null default 'b'     check (request_type = 'b'),   primary key (request_id),   foreign key (request_id, request_type)      references requests (request_id, request_type) on delete cascade,   param_i char(1) not null,   param_j char(1) not null ); 

repeat each type table.

i create 1 updatable view each type. views join table "requests" 1 type table. application code uses views instead of base tables. when that, makes sense revoke privileges on base tables. (not shown.)

if don't know type is, there's no alternative running 1 query type, , query select or update.

select request_type requests request_id = 42;   -- returns 'a'. i'd use view type_a_only. update type_a_only set param_x = '!' request_id = 42; 

in own work, it's pretty rare not know type, happen sometimes.