Re: Design question regarding arrays

Michael Glaesemann wrote:
>
> On Aug 8, 2004, at 2:03 PM, A Gilmore wrote:
>
>> Regularly Ill have to search this db for records containing a
>> specific catagory. For example get all books belonging to the sci-fi
>> catagory, for the most parts the searchs will only look for a single
>> catagory. They would look like this :
>>
>> SELECT * FROM my_table WHERE 'sci-fi' = ANY (catagory);
>>
>> Is that bad design?
>
>
> Probably. I'd set up a categories table that lists the possible
> categories. Then create a books_categories table that lists the
> categories for each book. For example:
>
> create table books (
> book_id serial not null unique
> , title text not null
> );
>
> create table categories (
> category_name text not null unique
> );
>
> create table books_categories (
> book_id integer not null
> references books (book_id)
> on update cascade on delete cascade
> , category_name text not null
> references categories (category_name)
> on update cascade on delete cascade
> , unique (book_id, category_name)
> );
>
> Michael Glaesemann
> grzm myrealbox com
>

Thanks a lot of the detailed reply, its a huge help. Does bring me to
one other question Id like to clarify. Using this method Ill be doing
inserts into books_categories based on the ID of the latest INSERT into
the books table. Since I cannot really have a INSERT return a value
such as the serial used without getting into PL/pgSQL, how can I get the
ID of the book just inserted?

According to board threads I have found doing something like :

select book_id from books where book_id = currval('book_id_seq');

Could lead to problems if multiple inserts are occuring at the same time
since currval() may have changed since the insert, and transactions do
not prevent that. Is this correct, that transactions will not help, and
if so what is the most common way of dealing with this issue (since I
assume its common) ?

Thank you for any help.
A Gilmore