Thread: Indexes

Another little doubt:

I've already asked that in #postgresql at freenode, but I didn't
understand well.

I have two tables:

Books
- book_id
- name

Authors
- author_id
- name

One book can have many authors and one author can have many books. To
make that possible, I need a third table:

Intermediate
- book_id
- author_id

My question is about the indexes in Intermediate table. Is the
following index:

CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);

enough for every query I want to perform? Or should I need

CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);
CREATE UNIQUE INDEX bar ON Intermediate(book_id);
CREATE UNIQUE INDEX baz ON Intermediate(author_id);

too?

Thank you very much. Bye!

On Jan 18, 2006, at 4:53 , Silas Justiniano wrote:

> CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);

You'll want this index for normalization.

> CREATE UNIQUE INDEX bar ON Intermediate(book_id);
> CREATE UNIQUE INDEX baz ON Intermediate(author_id);

You probably don't want these two indexes unless you want to have
only one entry for each book, or one entry for each author. Many
books have multiple authors and many authors write more than one
book, so you probably don't want to restrict this.

Michael Glaesemann
grzm myrealbox com