foreign key to multiple tables depending on another column's value

From: Rodrigo Rosenfeld Rosas <rr(dot)rosas(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: foreign key to multiple tables depending on another column's value
Date: 2013-05-29 13:58:09
Message-ID: 51A60971.8060608@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

Sorry, I wasn't sure what list I should be sending this question to...

I have a multi-tenant-like application. We have a fields tree that we
call a template, with something like this:

fields(id, parent_id, name)

And several other related tables. Since we started to support multiple
templates we created another schema per added template, each having the
same fields and related tables. Now I was asked to create another
special template that doesn't contain the values for those fields
(stored in separate tables in the regular schemas) and that would allow
us to map some field to some specific field on each of the aggregate
templates.

So, to exemplify, let's suppose we have regular templates "template1"
and "template2" and a special template "special1". This is what I want
to achieve:

I'll use the name (id, parent_id) notation.

template1 (table name is template1.fields):

- Target (1, null)
- Name (2, 1)
- Country (3, 1)

template2 (table name is template2.fields):

- Borrower (1, null)
- Name (12, 1)
- Country (13, 1)

special1 (table name is special1.fields):

- Company [maps to either Target or Borrower] (20, null)
- Name (21, 20)
- Country (22, 20)

So, now the idea is to map those fields using another table:

special1.mapped_fields(field_id, template_name, mapped_field_id), with
records like:

(20, 'template1', 1)
(20, 'template2', 1)
(21, 'template1', 2)
(21, 'template2', 12)
(22, 'template1', 3)
(22, 'template2', 13)

But the problem, as you have noticed is that I can't use a foreign key
with mapped_field_id, because the referenced table will depend on the
value of template_name.

I know I could use a trigger, or some check constraint maybe, to ensure
the field exists upon insert (or update), but I can't ensure the
database will become inconsistent in case I remove a mapped field from
the other schema.

Now I can finally explain my question: is it possible that I set some
sort of foreign key whose referenced table and column would depend on
the value of another column?

Thanks in advance,
Rodrigo.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message fburgess 2013-05-29 14:44:19 Re: [PERFORM] Very slow inner join query Unacceptable latency.
Previous Message Peter Eisentraut 2013-05-29 13:44:00 Re: [GENERAL] pg_upgrade -u

Browse pgsql-sql by date

  From Date Subject
Next Message Marc Mamin 2013-05-29 15:07:07 Re: reduce many loosely related rows down to one
Previous Message Bill MacArthur 2013-05-28 20:58:53 Re: reduce many loosely related rows down to one