Re: Help with complex db design concepts

From: Todd Benson <>
Date: 9 May 2002 10:29:13 -0700
Message-ID: <> (zaq) wrote in message news:<>...
> Consider a database where my tables represent spatial entities, such
> as buildings, roads, utility poles, bodies of water, etc... This set
> of entities is fixed, but over 1000.
> Now consider a database designed to associate documents with one or
> more of these entities. Users will need to search all documents, as
> well as search for documents associated with spatial objects.

I have run into a similar situation, but be careful. If you have to ask a question like, what is the general location (i.e. state) of all of the spacial entities associated with a particular document? Then it will be difficult. You will either have to outer join with every spacial entity table and look for non nulls (that's more than 1000 tables you said!), or you will have to go through a procedure:

  • select all relations to this document
  • okay, check what type is the first relation
  • select location from the table that corresponds to the entity type
  • okay, check what type is the next relation
  • select location from the table that corresponds to the next entity type
  • etc.

There are probably many ways to deal with this, but they depend on what type of questions you need to ask about the data.

Todd Received on Thu May 09 2002 - 19:29:13 CEST

Original text of this message