Generally, indexes are useful on columns that:
a) are fairly selective (i.e. have few rows per value, relative to the total number of rows in the table)
b) are used in the WHERE clause of queries in a selective manner
Primary/unique keys should always be indexed (in fact, you can't avoid indexing them in some DBMSs at least).
Foreign keys are often a good candidate, because they often appear in join conditions. (In Oracle you should index all foreign keys as a rule, for other reasons concerning locking).
Other non-key columns may be indexed if a benefit is likely. For example, an index on employee.surname is probably useful for name-based searches, but an index on employee.salary is unlikely to be very useful (how often do people search for employees where salary = 1000 for example?)
You should read the documentation and books relating to your specific DBMS, because the best indexing strategy may be different for different DBMSs.
Just an added notefrom an old hand: In general after you have created an index on the primary or significant business field, don't create any more indices unless and until they are clearly needed (i.e. performance is bad).
Often even though it looks like an index is required, the database optimiser may not use it - for example if the data volume is small, a full table pass may be faster. (Actually if the data volume is small enough you may not need any indexes.)
This of course means you need to do adequate performance testing before the database goes into production, with suitable volumes of data.