> Silas Justiniano > Jan 17, 5:53 pm show options > Newsgroups: pgsql.general > From: "Silas Justiniano" <sila...@xxxxxxxxx> - Find messages by this > author > Date: 17 Jan 2006 11:53:37 -0800 > Local: Tues, Jan 17 2006 5:53 pm > Subject: Indexes > Reply | Reply to Author | Forward | Print | Individual Message | Show > original | Remove | Report Abuse > > hi. > > 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? It is fairly normal for intermediate tables of this sort to just need the first of the four indexes that you indicate, e.g. CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id); The other two indexes would rule out having either: a) An author that writes more than one book, or b) A book with multiple co-authors. That makes them both poor ideas, I'd think... -- (format nil "~S@~S" "cbbrowne" "acm.org") http://linuxfinances.info/info/ Rules of the Evil Overlord #80. "If my weakest troops fail to eliminate a hero, I will send out my best troops instead of wasting time with progressively stronger ones as he gets closer and closer to my fortress." <http://www.eviloverlord.com/>
