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

Question

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.

Answer 1

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.