Thread: Foreign keys and inheritance
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? TIA! kj
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
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? > It seems that inheritance is precisely what you want. WRT yout table T you should be able to join to B in the same way you would join to A. But perhaps you should give an example of both B & T (and maybe A). brian
Kynn Jones escribió: > 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? It would be the right tool if the FKs worked :-( Sadly, they don't. alvherre=# create table foo (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" cCREATE TABLE alvherre=# create table bar (a int not null references foo); CREATE TABLE alvherre=# create table baz () inherits (foo); CREATE TABLE alvherre=# insert into baz values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 (1 row) alvherre=# insert into bar values (1); ERROR: insert or update on table "bar" violates foreign key constraint "bar_a_fkey" DETAIL: Key (a)=(1) is not present in table "foo". This is a Postgres shortcoming, but I don't think there's anybody working on fixing it, so don't hold your breath. Uniqueness also fails in inheritance: for example alvherre=# insert into foo values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 1 (2 rows) (Note that column is the PK) -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Investigación es lo que hago cuando no sé lo que estoy haciendo" (Wernher von Braun)
On Mon, 2007-11-19 at 17:19 -0300, Alvaro Herrera wrote: > Kynn Jones escribió: > > 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? > > It would be the right tool if the FKs worked :-( Sadly, they don't. > I don't think it's that bad of a situation. It would be great if PostgreSQL did support keys across tables, but it's not necessary for a good design in his case. The difference between using inheritance and just using multiple tables (like the alternative that I suggested) is the difference between vertically partitioning and horizontally partitioning. Both seem like good choices to me. Regards, Jeff Davis