« Return to Thread: Database design problem

Re: Database design problem

by Paragon Corporation-2 :: Rate this Message:

Lisa,

From what you are saying, it sounds like your tree is balanced, so why can't you relate at each level? Basically forget you've got an inheritance going (it actually isn't relevant if you have a balanced tree) and treat them as you would treat any other 2 sets of tables.

If you were trying to relate say surveytype with surveyobs then you would have problems.

Or ah you asking how to automate the foreign key primary key relationships?

Leo

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Lisa Rockwell
Sent: Monday, July 06, 2009 2:47 PM
To: postgis-users@...
Subject: [postgis-users] Database design problem

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

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

« Return to Thread: Database design problem