You raise a good question. There is some mixing of terms (front-end designer, front-end developer, which are very often not the same) which made the answer for me a little less concise that maybe you would prefer (sorry).
Separating the terms out could clarify the answer a bit, as I think of them as quite separate and requiring non-overlapping expertise that are quite often not done by the same person, as both fields require years of experience in order to gain expertise.
For the record, I am a front-end developer and no one in their right mind wants me designing their website. My responsibility is PHP/HTML, CSS and JavaScript. I don't create the Photoshop files; they get handed to me to cut up into HTML/CSS.
Web designers are typically responsible for transforming a client's requirements and branding into a graphical layout (most times, a Photoshop PSD). They're often formally trained in design (line, colour, form). I think understanding principles of design is probably more important that choosing a piece of software in this field and would the first place to start if you haven't already. Since you say, "I really don't know where to start when it comes to client side UI (as in designing pages that look good)," I believe this would be the first logical starting point to gain proficiency in this field.
In terms of workflow/tools, designers typically use software like Balsamiq Mockups or Keynote (http://keynotekungfu.com/) to first generate mockups so clients can approve a UI/layout without getting bogged down in colour choices or textures. These might be very quick sketches meant to inform the design, or they can be very involved wireframes (a separate deliverable in itself), that meticulously map out the page flow, layout and functionality.
Designers also use techniques like mood boards or design tiles to capture the branding and 'look and feel' of a site before committing to a fully-fledged design. Once the client decides on a palette, UI, and layout for the site, the designer then uses Photoshop, or less often, Fireworks or another graphics program, to create a completed design.
In the majority of shops, and in the experience of most professional freelancers I know, the front-end developer (who is usually but not always a different person), then takes the completed PSD and cuts it into working HTML/CSS. What tool in used is a matter of personal preference as well as a subject of animated debate. I like Coda. Others like TextMate or TextWrangler. Not many people in either profession that I'm met use PCs. It is a Mac world. PCs are for the suits, man.
My own workflow as a front-end developer includes:
- Firebug and Chrome's Developer Tools for CSS development.
- Coda for authoring HTML/CSS/JavaScript/PHP. It's a very lightweight IDE that includes a CSS editor, code completion and syntax highlighting for all the above.
- Photoshop for cutting the PSD into HTML.
- The Firefox add-on MeasureIt for taking pixel measurements in the browser.
- The Firefox add-on Pixel Perfect for creating exact pixel matches of the delivered PSD files (designers will be very happy with you for respecting their original designs).
- a Windows VM for testing in IE. I use VMWare Fusion, which is generally good but super processor intensive, but there is also Wine, which is free and other good choices.
- the Firefox add-on Web Developer Toolbar for a variety of tasks (checking how a site looks with JavaScript disabled, theming at the print stylesheet output, checking for accessibility, viewing the semantic structure of a site etc.).
- Firefox add-on ColorZilla for monitoring color, grabbing hex codes from sites (very, very useful)
I've also used Safari's iPad/iPhone view to test the site using a mobile user-agent.
Those are some of the tools I use at least. I'd be interested in other techniques.