[postgis-users] Database design problem

[Lisa Rockwell]

I am having problems trying to model a relationship in my database and I
heard that the people on this forum enjoy a challenge.

I have 2 supertype tables, currently in a 1 to many relationship, that use
table inheritance and both have many subtypes. A record in the table 'Survey
types' describes records in the table 'Survey Observations' (a spatial
table).

So, 'Survey types' has children 'Survey type A', 'Survey type B', etc., and
'Survey Observations' has children 'Survey Obs A', 'Survey Obs B', etc. My
problem is that I need 'Survey type A' to relate to 'Survey Obs A', 'Survey
type B' to 'Survey Obs B', etc., where there will be a growing number of
subtype tables over the life of the database, as we expand and preform new
types of surveys.

I cannot think of a good way to connect these tables and it is probably the
most important relationship in the database.

I can send you a jpg that better explains this.

Thanks,
Lisa
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090706/9e6f0133/attachment.html>