Re: Foreign keys and inheritance

On Mon, 2007-11-19 at 14:36 -0500, Kynn Jones wrote:
> I have two classes of objects, A and B, where B is just a special case
> of A. (I.e., to describe a B-type object I need to specify the same
> fields as for an A-type object, plus a whole bunch additional fields
> specific to B alone.) Furthermore, there's a third class T that is in
> a many-to-one relation with A (and hence also B) objects.
>
> The question is, what's the "best practice" for implementing this
> situation in PostgreSQL. My first idea was to define B as inheriting
> from A, which is OK, except that I have not figured out how to
> implement the reference from T. Is inheritance indeed the right tool
> for this problem, or should I use a different approach?
>

I would probably do something like:

CREATE TABLE A (a_id INT PRIMARY KEY, a_attr text);
CREATE TABLE B (b_id INT PRIMARY KEY,
a_id int references A(a_id) UNIQUE, b_attr text);
CREATE TABLE T (t_id INT PRIMARY KEY,
a_id int references A(a_id), t_attr text);

I can't tell whether you mean that every A has many T or vice versa, but
minor modification will make it work in the opposite direction.

To look at all A objects, you just look in table A.
You can do "A NATURAL JOIN T" to realize the many-to-one relationship
from A to T.
You can do "A NATURAL JOIN B" to see all B objects (which have a_attr
since they are a special case of A).

This is a normal relational design that is very flexible and doesn't
require the PostgreSQL-specific "INHERITANCE" feature. You don't need to
use natrual joins of course, it was just easier for this example.

Regards,
Jeff Davis