Re: Help with Arrays and References

Somebody called 'Stephan Szabo' tried to say something! Take a look:
> On Mon, 24 Jun 2002, William N. Zanatta wrote:
>
>
>>Hi,
>>
>>   I'm starting on PostgreSQL so please be patient; =]
>>
>>   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

    I would like to hear you as I used to work with mysql which is not
too amazing as postgresql. The more I read the documentation, the more I
get crazy, PostgreSQL is very powerful but, my poor concepts doesn't let
me bring it to my life. =]

    Thanks,

William

--
Perl combines all of the worst aspects of BASIC, C and line noise.
                 -- Keith Packard