Re: Help with complex db design concepts

From: James <jraustin1_at_hotmail.com>
Date: 29 May 2002 17:25:16 -0700
Message-ID: <a6e74506.0205291625.7be061bd_at_posting.google.com>

> Consider a database where my tables represent spatial entities...
> ...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.

This is an excellent problem to demonstrate the advantages of an oodb which can be:

1. Much simpler. Would someone like to model some representative data?
2. Much faster. Would someone like to perform a benchmark?
3. More generic. Would someone like to compare the ability of algorithms to survive schema changes and addition/deletion of entity types?

In rdb solution1 below, problems come up in joining T_EntityDocMap with appropriate T_EntityX. If you add/delete a T_EntityX, the sql will need tweaking.

T_Doc

T_Entity1
T_Entity2
...
T_EntityX

T_EntityDocMap
  DocId
  EntityTableId
  EntityId

In rdb solution2 below, problems come in joining T_Entity with appropriate T_EntityXProps. If you add/delete T_EntityXProps, the sql will need tweaking.

T_Doc

T_Entity

T_Entity1Props
  EntityId
...
T_EntityXProps
  EntityId

T_EntityDocMap
  DocId
  EntityId

Below is a solution using XDb, a simple oodb (www.xdb1.com).

Note:
"." is a property.
"[]" is class of object.
-> is a reference to an object.
A property can have multiple values.

Example Classes:
Note SpEntity is the ancestor class of all other Spacial Entity Classes
which allows generic queries shown below.

---

Doc

SpEntity

City [SpEntity]
.Doc

Road [SpEntity]
.Doc

Building [SpEntity]
.Doc

UtilPole [SpEntity]
.Doc

Lake [SpEntity]
.Doc

Pond [SpEntity]
.Doc

River [SpEntity]
.Doc

Stream [SpEntity]
.Doc

Etc [SpEntity]
.Doc

Example Instances

---

DocFolder
 Doc1
 Doc2
 Doc3
...

 DocX

City1
  .Doc
    ->Doc1
  Road1
    .Doc
      ->Doc2
    BldgFolder

      Bldg1
        .Doc
          ->Doc1
          ->Doc3
      Bldg2
        .Doc
          ->Doc6
          ->Doc9
          ->Doc8       
    PoleFolder
      Pole1
        .Doc
          ->Doc6
      Pole2
        .Doc
          ->Doc7
          ->Doc8
    Pond1
      .Doc
        ->Doc4
        ->Doc5

Example Object Queries (www.xdb1.com/HowTo/CreateQuerySimple.asp)

---

  Query - Find where Doc7 is referenced within City1.     Type
      Select
    SearchMode

      Instances
        ->Doc7
    Criteria
      AncestPar
        ->City1
      ObjectType
	->Ref

  Query - Find in City1, SpEntity instances whose property Doc is Doc7.

    Type
      Select
    SearchMode

      Instances
        ->SpEntity
    Criteria
      AncestPar
        ->City1
      Property
        .Doc = Doc7

The above object queries will survive schema changes and addition/deletion of entity types (ie Bldg, Pond). Received on Thu May 30 2002 - 02:25:16 CEST