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

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.

Solution

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.)