One of the greatest things about the Drupal CMS is that it can support *any* look and feel you want it to in a just click of a button. Or at least it can once you have the theme you want designed and "Drupalized".
Themes/designs are a very unique part of a web site, because they they simulataneously are "just" a wrapper for the content on your site - while at the same time being *the* main element which keeps your site from just being a bunch of text splattered on a page (screenshot of this page without an active theme).
So, the visual design of your site is one thing in itself, but it's only a beginning, and in a proper site construction a theme should start out literally as a 'pretty picture', mocked up in Photoshop for easy experimentation/changes.
What happens after this stage in theme development is an entirely different case altogether, because now we're moving from a 'pretty picture' to working code, which will affect browser compatibly (do Internet Explorer 7 users see your page as a blob?), download speed, and usability (does the link to the contact page not work for Firefox users?).
The coding stage is where expertise and testing become necessary in order to keep a your design from becoming a nightmare in terms of usability, compatibility, and flexibility.
So, a good theme is not coded to not just look like the pretty picture you started out for one browser, but to also:
- look and behave correctly across a wide variety of web browsers - now and for the future
- be flexible enough to provide future customization and extensibility. Having a theme that falls apart or cannot support at least minor revisions to the layout will be very limiting later on if you need to change things up.
HigherVisibility Theme Library
Hundreds of high quality themes, for dozens of categories. Browse and brainstorm - themes available in raw format, or professionally Drupalized
Open Source Web Design
Browse Free Web Design Templates (non-Drupalized)
The Drupal Theme Developer's handbook is a good place to go for information on developing the code for your own theme.