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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
