How to determine if an Index is required or necessary
I use Jason Strate's index analysis scripts (Old location). They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table. 

Most importantly, though, it's about making sure the application responds fast enough for the users.

Update: Jason Strate's index analysis blog articles for newer scripts (New location)

Double Update: These days, I use sp_BlitzIndex® when performing index analysis.