1

We have a Postgres setup with multiple tables representing different parts and stages of a project, 4 of them at the moment but potentially more in the future. We want to make a Task table with a foreign key to any of these tables. Preferably like this:

CREATE TABLE stage1(
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage2 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage4 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE task (
id SERIAL PRIMARY KEY,
description TEXT,
foreignkey_table VARCHAR(20),
foreignkey_id INTEGER);

where the column named foreignkey_table can indicate the "parent" table and the foreignkey_id will indicate the "parent id".

These stages 1 to 3 are to make the example simple but they can in no way be joined as one. Think of them as "preparation stage", "execution stage" and "evaluation stage". Or as completely different tables like "users", "companies" and "activities" each with its own specific not overlapping content.

How can I do this?

I have read some posts telling it is not possible but they were quite old so I'm hoping a solution is implemented after these posts. I also found a potential solution on Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL), and this is my current best fallback option.

6

I don't see the need for multiple stage tables at all (tip: if you have increasing numbers on columns or tables you probably violated normalization rules).

I would create a single stage table that contains the information which stage that is:

CREATE TABLE stage
(
  id SERIAL PRIMARY KEY,
  level integer not null check (level between 1 and 4),
  description TEXT
);

CREATE TABLE task 
(
  id SERIAL PRIMARY KEY,
  description TEXT,
  stage_id integer references stage
);

If you get new stages in the future, all you need to do is add new rows to the stage table.

  • To keep this example somewhat simpler than the real situation i used 3 different stages but these MUST be different tables, There is way of joining these tables – K.Mooijman Oct 4 '17 at 12:26
  • 4
    @K.Mooijman: why do you think those "must" be different tables? With the information given, that is absolutely useless (and wrong). You should provide the real problem in your question, not some over-simplified structure that leads to a completely different solution (but I can't think of anything that would warrant more then one stage table - that seems to completely violate 1NF) – a_horse_with_no_name Oct 4 '17 at 12:27
  • I edited the main question to explain – K.Mooijman Oct 4 '17 at 12:33
  • 1
    You can certainly combine them. Either by putting all their attributes in the single stage table (and making them nullable) or by keeping the extra tables and the extra attributes as they are and making their PK from serial to normal int and having a foreign key constraint to the stage table. This way they can be separate tables and you can still reference from other tables (like the task here) the main stage table. The design pattern is sometimes called superclass/subclass (also search for inheritance and you can find many examples). – ypercubeᵀᴹ Oct 4 '17 at 17:37
  • 1
    Also called supertype/subtype. See this very good answer by MDCCL: How to model an entity type that can have different sets of attributes? – ypercubeᵀᴹ Oct 4 '17 at 17:50
1

I'm in no position to judge if the desired design you try to achieve is proper or desirable. To me it looks like it will not be rated as a clean table design.

This proposal uses a base-table which provides a relation for the IDs a stage can have. No matter what stage an entry belongs to, the ID is stored in a table called STAGE_BASE. The table might look similar to

create table STAGE_BASE (ID serial primary key);

Your single stages could be created as

create table STAGE1(fk_id INTEGER references STAGE_BASE, description TEXT);
create table STAGE2(fk_id INTEGER references STAGE_BASE, description TEXT);

where fk_id is a foreign key to STAGE_BASE(ID).

The task table stays the same as you proposed, but the foreign key now references to the same ID as all STAGE tables:

CREATE TABLE task (
  id SERIAL PRIMARY KEY,
  description TEXT,
  foreignkey_table VARCHAR(20),
  foreignkey_id INTEGER references STAGE_BASE);

Instead of a VARCHAR(20) data type for the column foreignkey_table you could use an INTEGER as stage number, which could be used in conjunction with yet another table, e.g. STAGES where the stage number references via foreign key constraint to this table.

Note that this is not a clean solution, and the query-statements you will have to write bear witness of the mess you will create with this table design. This is most obvious in the select-statements because there is no clean and automatic way of joining all information together based on the contents of the table TASK. You could join all stages via outer-join, though, but it still is not performing well and still is bad designed.

On the other hand, if you know in each stage which stage-table you have to join, and this is fixed (not dynamic) during program execution, you might have a not beautiful but working solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.