Re: How best to represent relationships in a database generically?

Lincoln Yeoh wrote:
> Hi, importantly do searches and other processing by those relationships.
>
> So, what would be the best way to store them so that a search for the
> relationship like "grass is to cow", will also turn up cow is to
> tiger, and goat is to tiger, and fish is to penguin (and penguin is to
> bigger fish ;) ), and electricity is to computer. And a search for cow
> is to goat, could turn up tiger is to lion, and goat is to cow.
>
> Is the only way to store all the links explicitly? e.g. have a huge
> link table storing stuff like obj => cow, subj => grass, type =>
> consumes, probability=90% ( => means points/links to). Or even just
> have one table (links are objects too).

Hi

This is a generic database design problem rather than a Postgres or SQL
one, but here goes

Excuse ASCII art..

What you really have is a multi - multi relationship, such as....


    A  <--->  B


Where A is a table containing grass, cow, fish
and B is the table containing cow, tiger and penguin

I know, A and B are the same table, so the multi - relationship is in fact

   A <----> A

As you cannot have a multi-multi relationship in a RDBMS, you need a
"link" table...

  A  ---> C <----B

or more precisely

 A  ---> C < --- A

This would be represented as tables as something like

create table thingy (
   thingy_key varchar(12)  primary key,
   thingy_desc  varchar(30)
   ....
  );
or whatever

 and...

create table munchies (
  eater varchar(12) not null,
  dinner varchar(12)  not null
  probablility_pc number(4,2)
  constraing pkey_munchies primary key(eater, dinner) );

or whatever, where "eater" and "dinner" are foreign keys for "thingy_key"

The munchies table can get big, but do not worry about that.  It is
small and RDBMS (especially Postgres) should handle it well even on a
smallish machine.

Hope that makes sense

Eddy