No subject

Thu Jun 11 13:32:49 EDT 2009

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 at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Lisa
Rockwell
Sent: Monday, July 06, 2009 2:47 PM
To: postgis-users at postgis.refractions.net
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

------=_NextPart_000_000B_01C9FE4D.769FF0A0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.6001.18248" name=3DGENERATOR></HEAD>
<BODY>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>Lisa,</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>From what you are saying, it sounds like your =
tree is=20
balanced, so why can't you relate at each level?&nbsp; Basically forget =
you've=20
got an inheritance going (it actually isn't relevant if you have a =
balanced=20
tree) and treat them as you would treat any other 2 sets of =
tables.&nbsp;=20
</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>If you were trying to relate say surveytype =
with surveyobs=20
then you would have problems.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>Or ah you asking how to automate the foreign =
key primary=20
key relationships?</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D406551719-06072009><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>Leo</FONT></SPAN></DIV><BR>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr align=3Dleft>
<HR tabIndex=3D-1>
<FONT face=3DTahoma size=3D2><B>From:</B>=20
postgis-users-bounces at postgis.refractions.net=20
[mailto:postgis-users-bounces at postgis.refractions.net] <B>On Behalf Of =
</B>Lisa=20
Rockwell<BR><B>Sent:</B> Monday, July 06, 2009 2:47 PM<BR><B>To:</B>=20
postgis-users at postgis.refractions.net<BR><B>Subject:</B> [postgis-users] =

Database design problem </DIV>
<DIV></DIV>I am having problems trying to model a relationship in my =
database=20
and I heard that the people on this forum enjoy a challenge. I =
have 2=20
supertype tables, currently in a 1 to many relationship, that use table=20
inheritance and both have many subtypes. A record in the table 'Survey =
types'=20
describes records in the table 'Survey Observations' (a spatial=20
table). So, 'Survey types' has children 'Survey type A', 'Survey =
type B',=20
etc., and 'Survey Observations' has children 'Survey Obs A', 'Survey Obs =
B',=20
etc. My problem is that I need 'Survey type A' to relate to 'Survey Obs =
A',=20
'Survey type B' to 'Survey Obs B', etc., where there will be a growing =
number of=20
subtype tables over the life of the database, as we expand and preform =
new types=20
of surveys. I cannot think of a good way to connect these tables =
and it=20
is probably the most important relationship in the database. I =
can send=20
you a jpg that better explains=20
this. Thanks, Lisa </BODY></HTML>

------=_NextPart_000_000B_01C9FE4D.769FF0A0--