0

Is the following DB-schema ok?

  • REQUEST-TABLE

REQUEST-ID | TYPE | META-1 | META-2 |

This table stores all the requests each of which has a unique REQUEST-ID. The TYPE is either A, B or C. This will tell us which table contains the specific request parameters. Other than that we have the tables for the respective types. These tables store the parameters for the respective requests. META-1 are just some additional info like timestamps and 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 is the foreign key into the REQUEST-TABLE.

Is this design normal/best-practice? Or is there a better/smarter way? What are the alternatives?

It somehow feels strange to me, having to do a query on the REQUEST-TABLE to find out which TYPE-TABLE contains the information I need, to then do the actual query I'm interested in.

For instance imagine a method which given an ID should retrieve the parameters. This method would need to do 2 db-access. - Find correct table to query - Query table to get the parameters

Note: In reality we have like 10 types of requests, i.e. 10 TYPE tables. Moreover there are many entries in each of the tables.

Meta-Note: I find it hard to come up with a proper title for this question (one that is not overly broad). Please feel free to make suggestions or edit the title.

dingalapadum
  • 2,077
  • 2
  • 21
  • 31

2 Answers2

0

The phrase you may be looking for is "how do I model inheritance in a relational schema". It's been asked before. Whilst this is a reference to object oriented software design, the basic question is the same: how do I deal with data where there is a "x is a type of y" relationship.

In your case, "request" is the abstract class, and typeA, TypeB etc. are the subclasses.

Your solution is one of the classic answers - "table per subclass". It's clean and easy to maintain, but does mean you can have multiple database access requests to retrieve the data.

Community
  • 1
  • 1
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • ah. ok. It feels to me like OO design maps badly to relational databases... It seems like there is no "perfect" solution... anyway. should we close this as duplicate? or should I rather delete this question altogether? The question you are linking has some good resources but I don't find the answers to be good in the SO-sense (complete, self-contained, non dependent on external resources, etc...).. not quite sure what to do. – dingalapadum Feb 05 '16 at 16:16
  • If you add some ways to measure "good", I can help evaluate the options in front of you... – Neville Kuyt Feb 05 '16 at 16:44
  • Don't confuse data modeling (which deals with the organization of facts) with OOP / software engineering (which deals with the organization of software systems). – reaanb Feb 05 '16 at 21:42
0

For exclusive types, you just need to make sure rows in one type table can't reference rows in any other type table.

create table requests (
  request_id integer primary key,
  request_type char(1) not null 
    -- You could also use a table to 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 they 
  -- did, they might reference the wrong type. 
  unique (request_id, request_type)
);

You need that apparently redundant unique constraint so the pair of columns can be the target of a 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 that only 'A' can be stored in the request_type column. The foreign key constraint guarantees that each row will reference an 'A' row in the table "requests". Other type tables are 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 for each type table.

I usually create one updatable view for each type. The views join the table "requests" with one type table. Application code uses the views instead of the base tables. When I do that, it usually makes sense to revoke privileges on the base tables. (Not shown.)

If you don't know which type something is, then there's no alternative to running one query to get the type, and another query to select or update.

select request_type from requests where request_id = 42;  
-- Say it returns 'A'. I'd use the view type_a_only.
update type_a_only
set param_x = '!' where request_id = 42;

In my own work, it's pretty rare to not know the type, but it does happen sometimes.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185