As both other responders have said, don't get the Kindle if you want to do library ebooks. Overdrive is not compatible with the Kindle. I purchased a Sony PRS-300 (about the same price as the Kindle 3) for my brother so he could do library ebooks.
How it usually works: you'll need a library card with the library (or library system) that handles the ebooks. I am thinking of making a trip to Columbus, Ohio to get a library card there (open to all Ohio residents free of charge) so I can use their ebook system. There are some libraries that will allow non-resident access to their systems if you pay an annual fee.
See this thread
for further information.
Once you have the card, you log on to their website, 'check out' the book and load it onto your ereader. Typically, it'll 'expire' (ie - stop working) after about 3 weeks, or whatever the checkout period is.