Forum OpenACS Q&A: One to Many Relationship

Hi,


  Does anyone know if its possible to do a one-to-many relationship in postgresql?

  I have a table of objects, and another table with a column that may refer to zero or more objects in the first table, sort of like a List in Java

  Is that possible?

  Thankyou in advance
    Nathan

I don't think you need a mapping table for one-to-many.

create table types (
 type_id integer not null primary key
);

create table content (
 content_id integer not null primary key
 type_id integer not null references types
);

insert into types values (1);
insert into types values (2);

insert into content values (1,1);
insert into content values (2,1);

Now there is a one-to-many relationship beteen content and types, or a many-to-one relationship beteen types and content.

If you wanted to categorize the content, you might have a table of categories:

create table categories (
 category_id integer not null primary key
 category varchar(32) not null unique
);

insert into categories values (1,'good');
insert into categories values (2,'bad');
insert into categories values (3,'ugly');
-- and a mapping table:

create table content_category_map (
 content_id ...
 category_id ...
 constraint ccm_un unique (content_id,category_id)
);

insert into content_category_map values (1,1);
insert into content_category_map values (1,2);
insert into content_category_map values (2,1);
insert into content_category_map values (2,2);

Now you have a many-to-many relationship between content and categories.

You can enforce a mapping table to be one-to-many by setting a unique constraint on one of the columns.