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