Drum tuning is largely dependent on the style of music being played. A jazz kit will usually be tuned to exact pitches in a scale, such as the snare being tuned to the root, one tom tuned to the fifth, one to the third, one to the root an octave lower, etc. A jazz kit will also usually be tuned such that the top and bottom heads are in tune with each other. This results in a sound that resonates well. For jazz drummers that play more melodically (such as Joe Morello on Dave Brubeck's "Take Five"), this resonance and intonation is more important to the sound than, say, a more rhythmic drummer, such as Buddy Rich.
A rock kit, however, might be tuned higher than a jazz kit in order to give each drum a sharper sound with less resonance. The snare is usually tuned higher in order to cut through. The top and bottom heads are also sometimes tuned differently on each drum, such that the bottom head is slightly sharp or slightly flat compared to the top head. This cuts down on resonance and can give the drums a more attack-oriented sound. This technique is also used to direct the sound for mic'ing purposes. A drum with the bottom head tuned higher than the top head will reflect more of the sound upward, which may be desirable depending on the sound the engineer wants to get out of it. You'll notice that the snare, for example, on rock tracks tends to snap or pop sharply, whereas the snare on jazz tracks tends to sing out more. This comes down mainly to tuning.
As far as producing a track with drums that are tuned to the song, I don't think this is a necessity. Drummers don't re-tune their drum kits between songs when the key changes. It's probably desirable to have the drum kit in tune with itself, but its not necessary to pick drum samples or tune drums to specific notes that fit within the chord changes of a song. That doesn't mean you shouldn't try it, though. If that's the sound you're going for, then by all means try it. It's just not a common practice.