Link dying at the beginning, killed by Ganondorf. Zelda flees, and later finds out that it is her destiny to take up the Master Sword and reclaim her kingdom. And Link guides her as a ghost, Shiek-style or possibly Spirit Tracks style, but it's Zelda doing the butt-kicking.
The title for this new Zelda would probably be: The Legend of Red Dead Redemption: A Link to the Wild West
What if there were two possible endings to the game? One in wich he dies and another one (more difficult to acomplish) in wich he survives? Or maybe if the possible cause of his death is a sacrifice, the "good" ending could be the tragic one (if not for the sacrifice Hyrule isn't completelly saved) or his death could be on the "bad" ending and depends on the player's accomplishments through the game, or the ending where Link survives is kinda hidden, I don't know, many ideas xD
That would give the player the control on Link's destiny/decisions....
I think Link dying at the end of a game in a heroic sacrifice kinda of way would be epic, but at the beggining would be kinda strange... though Shdsie's idea seems really interesting.