That's a huge topic. There have been books written about it (note the bottom of this post 😀 ). Which indexes you place on the tables really depends largely on how the data is going to be retrieved, meaning, what kind of queries are written against it.

An entire book might be a bit much. Gail Shaw has recently written a couple of good articles over at Simple-Talk on how to identify poor performing procedures[/url]. Once you know what's running slowly, you can usually figure out where to put your indexes.