Re: Help with Arrays and References

On Tue, 25 Jun 2002, William N. Zanatta wrote:

> Somebody called 'Stephan Szabo' tried to say something! Take a look:
> > On Mon, 24 Jun 2002, William N. Zanatta wrote:
> >> I'm building a book library database in which I have a table
> >>TBL_FORMAT which keeps basic information on various file-formats (ie 1,
> >>PDF, Portable Document Format) and a TBL_BOOKS which I want to reference
> >>TBL_FORMAT. But the column Format inside TBL_BOOKS is an array, as a
> >>book may exist in one or more file types. My doubt is: If I make
> >>something like
> >>
> >> CREATE TABLE "tbl_books" (
> >> "id_book" int4 DEFAULT nextval('TBL_Books_ID_Books_Seq'::text) NOT NULL,
> >> "format" _int4 NOT NULL REFERENCES TBL_Format ON_UPDATE CASCADE,
> >> ...
> >>
> >> will the CASCADE action update my TBL_Books (Format) keeping the
> >>other values in the array or will it erase all and set the new Format value?
> >
> >
> > That shouldn't even be legal assuming that tbl_format's key is an int.
> > The two types must be comparable which isn't true of int4 and _int4.
> > You're probably better off with a details table with the book's id and
> > format's id and appropriate references.
> >
>
> Thanks Stephan,
>
>
> I wanted to avoid repeated lines of information just because of the
> 'format' column. Maybe I could create a specific data type for that but
> as it will be just a tiny small database, I will not spend my time.
> Anyway how would you do it? The idea is:
>
> - I have an electronic library.
> - I have books in more than one file type (ie. pdf and zip)
> - I want to keep it in the database, thus I'd have something like:
>
> -=[ table books ]=-
> bookName | format
>
> mybook | array(1, 3)
>
> -=[ table format ]=-
> id_format | format | description
> 1 | pdf | Portable Document Format
> 2 | txt | ASCII RAW Text
> 3 | zip | ZIP Compressed File
>

Generally speaking, I suggest a new table like:
create table book_format(
id_book int4 references tbl_books on update cascade
on delete cascade
id_format int4 references tbl_format on update cascade
on delete cascade
);

That might have data like:
id_book | id_format
1 | 1
1 | 3
to say that book 1 comes in pdf and zip.