Best method for a unique front page

The page-front-tpl.php is the most versatile approach, allowing you to construct the layout how ever you like. You can also add new regions and rename the divs to whatever you need as well. Much more efficient than trying to kludge something together using panels or other module based solutions.

While that is one approach there is nothing kludge like about panels. It has the advantage of allowing to control both the layout and content from the UI.

I'm sorry, I'm not dissing Panels in any way, it was my go-to solution for building until I needed to create some pixel perfect layouts. Creating your own divs using a custom page.tpl.php gives you finer grain control over how elements and blocks line up together, Panels can do it, but I was advised against using it for that purpose.

For the record, page-front-tpl.php doesn't work.

page-front.tpl.php does though.

With this approach, is it also possible to define css files only used on the frontpage ?

Yes,

You would create frontpage-specific div id's and classes to section off you content that you could target with CSS. So for example, here's the content area from Zen's page.tpl.php:

      <div id="content" class="column"><div class="section">

        <?php if ($mission): ?>
          <div id="mission"><?php print $mission; ?></div>
        <?php endif; ?>

        <?php print $highlight; ?>

        <?php print $breadcrumb; ?>
        <?php if ($title): ?>
          <h1 class="title"><?php print $title; ?></h1>
        <?php endif; ?>
        <?php print $messages; ?>
        <?php if ($tabs): ?>
          <div class="tabs"><?php print $tabs; ?></div>
        <?php endif; ?>
        <?php print $help; ?>

        <?php print $content_top; ?>

        <div id="content-area">
          <?php print $content; ?>
        </div>

        <?php print $content_bottom; ?>

        <?php if ($feed_icons): ?>
          <div class="feed-icons"><?php print $feed_icons; ?></div>
        <?php endif; ?>

      </div></div> <!-- /.section, /#content -->

Everything is wrapped in a div with an id of "content", with all the Drupal guts created with the php command ?php print ( print $highlight, print $messages, print $content_top, and the ever important print $content).

You duplicate the page.tpl.php file and rename the copy page-front.tpl.php (or use devel to find a candidate name) then you can change the order of the php printed items, wrap them in new divs, and add or delete them- adding new or altered CSS id's as you go to suit your theming needs in the new front page template.

How do I edit a Zen subtheme so the layout is different for the front page? I would like to use page-front.tpl.php with three columns and page.tpl.php with 2 columns (of different sizes, deleting the print of a column will not do the trick)

From what I can tell, I would like to have page-front.tpl.php call sidebars-front.css and and page.tpl.php use sidebars.css if this is possible (and will accomplish my goal).

I can speculate of how to do this (e.g. through page-front.tpl.php or my .info file), but my inexperience likely leads to poor speculation, and any assistance would be appreciated. Thanks.

@smrogers - you could do a lot of this using CSS.

If you use the block display settings so that blocks appear in sidebar-first, content & sidebar-second regions on the front page, and then make sure they only appear in two of the regions throughout the rest of your site - you could use the .front class to change the sizes of the sidebars / content area on the front page so the regions are have different widths and creating a different layout