How to define sql relationship to variable number of tables

Question

Let's hope my explanation is clearer than the title. I have a set of files. Each file contains a variable number of papers/forms. So I have a table called files, with an fid.

For the sake of simplicity, let's say we have only 3 different forms, each contains its own set of data. So I have 3 tables, FormA, FormB and FormC, with their primary keys Aid, Bid, and Cid respectively.

The file can contain for example, 2 A forms and 1 B form, or 1 of each form, or 3 A forms, 2 B forms, 2 C forms. You get the idea, variable number of forms, and might include more than 1 of the same type.

How to properly represent such relationship in SQL? If it matters, I'm using PostGreSQL.

I don't have time to write up a proper answer, but these keywords may help you: polymorphic associations, disjoint subtypes. This was answered on SO a couple of times, e.g.: http://stackoverflow.com/questions/561576/polymorphism-in-sql-database-tables http://stackoverflow.com/questions/7947218/polymorphic-association-foreign-key-constraints-is-this-a-good-solution — rkhayrov, Aug 06 '13 at 09:19
Maybe add a table, called `files`, that will contain id of the file (and any other data relevant to all the forms) and simply add FK from every form table to the main table. — Ihor Romanchenko, Aug 06 '13 at 09:53

score 1 · Accepted Answer · answered Nov 06 '13 at 11:47

In PostgreSQL here is how I would do this. Note I am using dangerous (non-beginner/advanced) tools, and it is worth understanding the gotchas.

Now since there are a number of tables here, the question is how we manage the constraints. This is a little convoluted but here is what I would do:

CREATE TABLE file (...);
-- add your tables for tracking form data here....
CREATE TABLE file_to_form (
    file_id int NOT NULL;
    refkey int NOT NULL,
    form_class char NOT NULL
    CHECK NOINHERIT (file_id IS NULL)
 );  -- this table will never have anything in it.

 CREATE TABLE file_to_form_a (
    PRIMARY KEY (file_id, refkey, form_class)
    FOREIGN KEY (refkey) REFERENCES file_a (form_id)
    CHECK (form_class = 'a')
 ) INHERITS (file_to_form);

 CREATE TABLE file_to_form_b (
    PRIMARY KEY (file_id, refkey, form_class)
    FOREIGN KEY (refkey) REFERENCES file_b (form_id)
    CHECK (form_class = 'b')
 ) INHERITS (file_to_form);

 -- etc

Now you have a consistent interface for showing which forms are associated with files, and can find them by searching the file_to_form table (which will function similar to a read-only view of all tables that inherit it). This is one of those cases where PostgreSQL's table inheritance really helps, if you take the gotchas seriously and put some thought into how to handle them.

How to define sql relationship to variable number of tables

1 Answers1