Many-to-Many in Postgres?

Question

I went with PostgreSQL because it is an ORDMBS rather than a standard relational DBMS. I have a class/object (below) that I would like to implement into the database.

class User{
    int id;
    String name;
    ArrayList<User> friends;
}

Now, a user has many friends, so, logically, the table should be declared like so:

CREATE TABLE user_table(
    id        INT,
    name      TEXT,
    friends   TYPEOF(user_table)[]
)

However, to my knowledge, it is not possible to use a row of a table as a type (-10 points for postgreSQL), so, instead, my array of friends is stored as integers:

CREATE TABLE user_table(
    id        INT,
    name      TEXT,
    friends   INT[]
)

This is an issue because elements of an array cannot reference - only the array itself can. Added to this, there seems to be no way to import the whole user (that is to say, the user and all the user's friends) without doing multiple queries.

Am I using postgreSQL wrong? It seems to me that the only efficient way to use it is by using a relational approach.

I want a cleaner object-oriented approach similar to that of Java.

Answer 1

Well, first off PostgreSQL absolutely supports arrays of complex types like you describe (although I don't think it has a TYPEOF operator). How would the declaration you describe work, though? You are trying to use the table type in the declaration of the table. If what you want is a composite type in an array (and I'm not really sure that it is) you would declare this in two steps:

CREATE TYPE ima_type AS ( some_id integer, some_val text);

CREATE TABLE ima_table
( some_other_id serial NOT NULL
, friendz ima_type []
)
;

That runs fine. You can also create arrays of table types, because every table definition is a type definition in Postgres.

However, in a relational database, a more traditional model would use two tables:

CREATE TABLE persons
( person_id serial NOT NULL PRIMARY KEY
, person_name text NOT NULL
)
;
CREATE TABLE friend_lookup
( person_id integer FOREIGN KEY REFERENCES persons
, friend_id integer FOREIGN KEY REFERENCES persons(person_id)
, CONSTRAINT uq_person_friend UNIQUE (person_id, friend_id)
)
;

Ignoring the fact that the persons table has absolutely no way to prevent duplicate persons (what about misspellings, middle initials, spacing, honorifics, etc?; also two different people can have the same name), this will do what you want and allow for a simple query that lists all friends.

Answer 2

I'm afraid you are indeed using PostgreSQL wrong, and possibly misunderstanding the purpose of Object-relational databases as opposed to classic relational databases. Both classes of database are still inherently relational, but the former provides allowances for inheritance and user-defined types that the latter does not.

This answer to one of your previous questions provides you with some great pointers to achieve what you're trying to do using the Postgres pattern.