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.