The best composition is probably one engineer whose passion lies in the pixels on the screen and another engineer whose passion is making bits fly really fast through servers.
Seriously though, having someone who comes from the aesthetic/UI side is a lot more useful than the generic "MBA" type who has a big vision and just needs someone to build the site. For instance, Flickr and Typepad both had a hacker/designer founding team. One of the recent YC teams had 2 designers and only one hacker and have been doing really well. The best example is probably Apple, SJ had some technical skill, but his real art was designing the UX, but had SW to handle the technical challenges. I'm all for the coder/coder team, but a great designer is not a bad addition!
I think what you want is someone who can do both the interaction design and the front-end coding - they have skills in making things look pretty, but can also write enough JavaScript/Python/C++/whatever needed to interface with the backend. Someone like Eric Costello of Flickr or Glen Murphy of Google Chrome.
My ideal founding team would have three members: a frontend engineer/designer, a backend engineer, and a business/marketing person. I think this is basically what Seth was saying, and I've also heard the RescueTime founders say that this is a great combination (it's what they have).
I wouldn't really want to go without the business person entirely, simply because somebody has to hold the entire product vision in their head, and the engineers usually have plenty to think about anyway. The tension between "C'mon guys, we can make this better" and "Okay, let's dive in and make this better" seems to be necessary for great creative works. That was really Steve Jobs' role in Apple - as I understand it, he did basically none of the coding. It also helps to have someone take care of all the interruption-driven tasks of running a company - following up on leads, talking to the press, drumming up excitement, getting feedback from potential customers, etc.
I would say that in the end, it's all about the user experience, and that unfortunately or not, comes from the design.
The cleverest app, or piece of coding is useless if the user doesn't get what they want from the experience of using it.
Brilliant design doesn't have to be flashy or clever, and of course, the simplest interfaces are always the best. There is a huge amount of skill and intelligence involved in taking what is a potentially complex idea or concept, and expressing to the user, not only how to use it, but also what the app is capable of, in a simple, graphical way.
There's been a of of discussion on HN about the new Sequoia site. I think I'm one of the few who think it's brilliant, because it already assumes a lot about its users, and that if they're on the site, they probably have a pretty good idea already of why they're there. The site is tailored to those people, they don't need to be hand-held with descriptions and explanations. This would be completely out of place in many other instances, but the designers knew their users, and what their needs were, so built accordingly. The web is a graphical interface for information, great design is crucial.
The OP made the point about a team...my point is that this has to a be a balance of skills, each member understanding their value and importance to the project, and understanding and valuing the importance of their colleagues. Teams made up of entirely one skill set will never be as productive as one with a wide range of skills.
Best 2 people to do a start-up?
A programmer and a business guy? No.
A programmer and a designer? No.
2 programmers. Yes.
Classic.