I'm building a model for holding a graph such as for a social network.
There is a table to hold the users (nodes), and another to hold the
acquaintances (edges). I'm wondering how best to define the edge table.

\,,,/
(o o)
------oOOo-(_)-oOOo------
-- type for the user identity
CREATE DOMAIN D_USR AS INTEGER NOT NULL CHECK ( VALUE > 0 );

CREATE TABLE USR ( -- users (nodes)
id D_USR,
data VARCHAR(10) NOT NULL -- some dummy data
);
ALTER TABLE USR ADD CONSTRAINT USR_PK PRIMARY KEY ( id );

CREATE TABLE FS ( -- acquaintances (edges)
a D_USR, -- user A
b D_USR, -- user B
status INTEGER DEFAULT 0 NOT NULL -- some dummy status
);
ALTER TABLE FS ADD CONSTRAINT FS_FK_A FOREIGN KEY ( a ) REFERENCES USR;
ALTER TABLE FS ADD CONSTRAINT FS_FK_B FOREIGN KEY ( b ) REFERENCES USR;
-- Ensure order to avoid duplicates +á la (1,2) and (2,1).
ALTER TABLE FS ADD CONSTRAINT FS_CHK_AB CHECK ( a < b );
-- Is this primary key necessary? At any rate, it is not symmetrical.
-- For a key on (a,b), should there be a symmetrical key on (b,a)?
ALTER TABLE FS ADD CONSTRAINT FS_PK PRIMARY KEY ( a, b );
-------------------------

The indexes backing the foreign keys for A and B on FS allow for
efficient retrieval of all edges involving any given node.

But I also have a primary key on FS, and it allows me to even more
quickly check whether there is an edge (a,b) where (a<b). In fact,
and of course, it is used in preference to the foreign key:

\,,,/
(o o)
------oOOo-(_)-oOOo------
SQL> set plan on;
SQL> select b from fs where a = 3 -- primary key not present yet
CON> union all
CON> select a from fs where b = 3;

PLAN (FS INDEX (FS_FK_A))
PLAN (FS INDEX (FS_FK_B))
SQL> commit; -- metadata update, adding primary key
SQL> select b from fs where a = 3 -- repeat query
CON> union all
CON> select a from fs where b = 3;

PLAN (FS INDEX (FS_FK_A, FS_PK))
PLAN (FS INDEX (FS_FK_B))
-------------------------

This isn't symmetrical, and probably it doesn't have to be. Having
worked with MySQL before (where the order of columns in a compound key
matters), I tried adding a UNIQUE constraint, but that fails:

\,,,/
(o o)
------oOOo-(_)-oOOo------
SQL> alter table fs
CON> add constraint FS_UQ UNIQUE( b, a );
Statement failed, SQLCODE = -607
unsuccessful metadata update
-Same set of columns cannot be used in more than one
PRIMARY KEY and/or UNIQUE constraint definition
-------------------------

So, is there any reason to worry about the lack of symmetry?

Or about anything else in this approach?

How would you define a table for edges such as in my example?

Thanks for your thoughts.

If you want to play with this, here's a script to clean up:

\,,,/
(o o)
------oOOo-(_)-oOOo------
DROP TABLE FS;
DROP TABLE USR;
DROP DOMAIN D_USR;
-------------------------

--
Michael Ludwig