Revitalising our northern cities may hold the key to easing the housing crisis, and the regeneration of Manchester is a model of success. Allison Heller learns the secret of its booming renewal programme since the 1996 city centre bomb