I'd suggest a bridge table for task-tree and anther one for task-pen.
create table tree
(
tree_id int primary key,
name text
);
create table pen
(
pen_id int primary key,
name text
);
create table task
(
task_id int primary key,
name text
);
create table task_tree
(
task_id int references task (task_id) on update cascade,
tree_id int references tree (tree_id) on update cascade,
primary key (task_id, tree_id)
);
create table task_pen
(
task_id int references task (task_id) on update cascade,
pen_id int references pen (pen_id) on update cascade,
primary key (task_id, pen_id)
);
insert into tree values (1, 'tree1'),(2, 'tree2'),(3, 'tree3');
insert into pen values (1, 'pen1'),(2, 'pen2'),(3, 'pen3');
insert into task values (1, 'task1'),(2, 'task2');
insert into task_tree values (1, 1),(2, 3),(1,2);
insert into task_pen values (1, 2),(2, 2);
select tk.task_id,
tk.name,
array_agg(tr.name) as tree,
array_agg(pn.name) as pen
from task tk
join task_tree tkt
on tkt.task_id = tk.task_id
join task_pen tkp
on tkp.task_id = tk.task_id
join tree tr
on tr.tree_id = tkt.tree_id
join pen pn
on pn.pen_id = tkp.pen_id
group by tk.task_id, tk.name
task_id | name | tree | pen
------: | :---- | :------------ | :----------
1 | task1 | {tree1,tree2} | {pen2,pen2}
2 | task2 | {tree3} | {pen2}
dbfiddle here
Update
If, as per comments, you prefer to use a single task table with two fields, tree_id and pen_id, you can set referential integrity in this way:
I've added a check constraint that ensures that one of tree_id, pen_id is null:
alter table task
add constraint ck_one_in_two_must_be_null check(tree_id is null or pen_id is null);
create table tree
(
tree_id int primary key,
name text
);
create table pen
(
pen_id int primary key,
name text
);
create table task
(
task_id int primary key,
name text,
tree_id int references tree (tree_id) on update cascade on delete restrict,
pen_id int references pen (pen_id) on update cascade on delete restrict
);
alter table task
add constraint ck_one_in_two_must_be_null check(tree_id is null or pen_id is null);
insert into tree values (1, 'tree1'),(2, 'tree2'),(3, 'tree3');
insert into pen values (1, 'pen1'),(2, 'pen2'),(3, 'pen3');
insert into task values (1, 'task1', 1, null),(3, 'task3', null, 3);
select tk.task_id,
tk.name,
tr.tree_id,
tr.name as tree_name,
pn.pen_id,
pn.name as pen_name
from task tk
left join tree tr
on tr.tree_id = tk.tree_id
left join pen pn
on pn.pen_id = tk.pen_id;
task_id | name | tree_id | tree_name | pen_id | pen_name
------: | :---- | ------: | :-------- | -----: | :-------
1 | task1 | 1 | tree1 | null | null
3 | task3 | null | null | 3 | pen3
insert into task values (2, 'task2', 2, 3);
ERROR: new row for relation "task" violates check constraint "ck_one_in_two_must_be_null"
DETAIL: Failing row contains (2, task2, 2, 3).
insert into task values (4, 'task4', 4, null);
ERROR: insert or update on table "task" violates foreign key constraint "task_tree_id_fkey"
DETAIL: Key (tree_id)=(4) is not present in table "tree".
insert into task values (5, 'task5', null, 6);
ERROR: insert or update on table "task" violates foreign key constraint "task_pen_id_fkey"
DETAIL: Key (pen_id)=(6) is not present in table "pen".
dbfiddle here
tree
andpen
tables. – Abelisto Feb 3 '18 at 18:41