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.
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.