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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
