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