Pretty much as others have described it; the discomfort varies by the location and 'density' of the tattoo (i.e. heavy colours/heavy shadows require more ink for a given area, and hence more needle time!).
I have two, one bikey (derailleur and chain on left shoulder/bicep) and one not (Orca sculpture on back of R shoulder). Both were original designs 'commissioned' by me from ideas/photos/a piece of chain…
I'm getting a bit addicted now; I can see the Orca turning into something bigger on my right arm sometime this year.
You definitely want to go with a design you like, and with an artist (note that word!) you are comfortable with and can communicate with. My studio (Lifetime in Derby) doesn't have flash on the walls; this (imho) is a good thing – I'm not looking to be put through a tattoo photocopier!
Expect the tattoo artist to want to spend some time talking to you but don't expect to wander in of a Saturday afternoon and have a chat, book a date in the diary. Lifetime in Derby (who've done both of mine) charges £70 per hour of needle time (i.e. the time actually spent tattooing), this price includes their set-up/clean up (which should take and hour each) and design time/consultation etc.
If you're not happy, for whatever reason, walk away. Laser removal (if possible) costs a lot more than a tattoo!
Buy some tattoo mags, go to some tattoo conventions. Become one of us!