Doug Chamberlin schrieb am 02.05.2010 um 19:57:12 (-0400):
[Re: [firebird-support] DDL for graph edges]

> Your scheme looks good to me except one thing. The relationship
> between A and B is not always equal going in both directions. If they
> are friends the "is a friend of" relationship is the same going both
> ways, but many, many relationships are not that way. For example,
> "works for", "father of", etc. Therefore, you may not want to
> constrain the second table to only allow one record between two people
> (where A < B in your schema). You may want to allow a record with B
> and A where a different relationship exists.

Thanks, Doug, My idea was to store directionality (and other edge
attributes) in "FS.status". I'd like to keep the table lean, so I
figured I might get by with just three integer fields, two for the
nodes connected by the edge, and one for the status, where each bit
has some defined meaning. And then a set of stored procedures to
manipulate the graph so the moving parts don't get exposed to the
API.

> In fact you may want to add a new field to FS that defines the actual
> relationship and is a foreign key to a relationship type table. All
> depends on how complicated you want to get with this and how far ahead
> you want to plan for future flexibility.

Right, good idea. At the moment, no other relationships are envisaged.
A separate table of the same structure for each kind of relationship
might also be an option. It will probably depend on the requirements
of the application.

What about the PRIMARY KEY( a, b ) ? It looks okay to me, but I'm
reading in Helen's book (on p.274) advice against compound primary
keys in Firebird. For those who have the book, does any of it apply
here?

I could certainly add an FS.id BIGNUM as a surrogate key, but somehow
it doesn't look better than the two USR.id INTEGER to me. In fact, it
looks like overhead in this particular case - but my thinking might be
flawed, or not firebirdish enough.

--
Michael Ludwig