Implement 1:N relation in postgreSQL (object-relational)

I'm struggling with postgreSQL, as I don't know how to link one instance of type A to a set of instances of type B. I'll give a brief example:

Let's say we want to set up a DB containing music albums and people, each having a list of their favorite albums. We could define the types 开发者_高级运维like that:

CREATE TYPE album_t AS (
Artist VARCHAR(50),
Title VARCHAR(50)
);

CREATE TYPE person_t AS (
FirstName VARCHAR(50),
LastName VARCHAR(50),
FavAlbums album_t ARRAY[5]
);

Now we want to create tables of those types:

CREATE TABLE Person of person_t WITH OIDS;
CREATE TABLE Album of album_t WITH OIDS;

Now as I want to make my DB as object-realational as it gets, I don't want to nest album "objects" in the row FavAlbums of the table Person, but I want to "point" to the entries in the table Album, so that n Person records can refer to the same Album record without duplicating it over and over.

I read the manual, but it seems that it lacks some vital examples as object-relational features aren't being used that often. I'm also familiar with the realational model, but I want to use extra tables for the relations.

Why you create a new type in postgresql to do what you need? Why you don't use tables directly?

With n-n relation:

CREATE TABLE album (
  idalbum integer primary key,
  Artist VARCHAR(50),
  Title VARCHAR(50)
);
CREATE TABLE person (
  idperson integer primary key,
  FirstName VARCHAR(50),
  LastName VARCHAR(50)
);
CREATE TABLE person_album (
  person_id integer,
  album_id integer,
  primary key (person_id, album_id),
  FOREIGN KEY (person_id)
    REFERENCES person (idperson),
  FOREIGN KEY (album_id)
    REFERENCES album (idalbum));

Or with a "pure" 1-n relation:

CREATE TABLE person (
  idperson integer primary key,
  FirstName VARCHAR(50),
  LastName VARCHAR(50)
); 
CREATE TABLE album (
  idalbum integer primary key,
  Artist VARCHAR(50),
  Title VARCHAR(50),
  person_id integer,
  FOREIGN KEY (person_id)
    REFERENCES person (idperson)
);

I hope that I help you.

Now as I want to make my DB as object-realational as it gets, I don't want to nest album "objects" in the row FavAlbums of the table Person, but I want to "point" to the entries in the table Album, so that n Person records can refer to the same Album record without duplicating it over and over.

Drop the array column, add an id primary key column (serial type) to each table, drop the oids (note that the manual recommends against using them). And add a FavoriteAlbum table with two columns (PersonId, AlbumId), the latter of which are a primary key. (Your relation is n-n, not 1-n.)

Sorry for answering my own question, but I just wanted to give some pieces of information I gained by toying around with that example.

ARRAY Type

I found out that the ARRAY Type in PostgreSQL is useful if you want to associate a variable number of values with one attribute, but only if you can live with duplicate entries. So that technique is not suitable for referencing "objects" by their identity.

References to Objects/Records by identity

So if you want to, as in my example, create a table of albums and want to be able to reference one album by more than one person, you should use a separate table to establish these relationships (Maybe by using the OIDs as keys).

Another crazy thing one could do is referencing albums by using an ARRAY of OIDs in the person table. But that is very awkward and really does not improve on the classic relational style.