There are a lot of points to be considered when selecting indexes.

The size of the table, if you only have at most a few hundred rows on
a table, you probably don't need any indexes as full scans will be
pretty fast. On the other hand have millions of rows and good indexes
will be essential unless you want your application to crawl.

Know you application. You would think if foreign keys are defined they
would need to be indexes, but not necessarily. Say one of your foreign
keys is only used for a report that runs once a month at a weekend.
Sure it will run faster with an applicable index, but do you need it
to? What about the overhead everytime the table is updated, and
needing to rebuild the index on a regular basis.

My approach is to build the obvious ones. If you have a customer table
for instance it's obvious that you need some kind of customer id
index. I would monitor table usage in development and even continue
into production using profiler to establish which would be the most
used indexes. If you just create all the indexes you think you might
need you could end up with 20, 30 or more indexes.

I try to be fairly ruthless with indexes, trying to keep no more than
5 or 6 max on a table. It's not always possible, but the more you add
the larger the space used, the more maint and the more overhead.

Hope this helps

John