It's a story of a hopeless romantic who gets his beautiful mistress pregnant while he is still with his wife.

And after finally leaving his wife when his illegitimate daughter has been born and fostered out, he ends up in a bizarre menage-a-trois with his lover and her elderly husband.

The sad denouement to the saga – which sounds like a very modern morsel of celebrity gossip – comes after his premature death when the lover turns to drink and gambling and dies in disgrace.

Surprisingly, this drama unravelled two centuries before the What Katie Did Next television genre was even thought of – and the main character was the leading popular hero of his day.

Nelson's Women: Philanderer or Family Man? is the title of a major new exhibition opening on Tuesday at the Nelson Museum on Great Yarmouth's South Quay.

Delving into Nelson's complex and scandalous private life, the story is illustrated with loan items which include his daughter Horatia Nelson's rocking horse and some of the baby clothes from his rock-solid childhood in Norfolk as the son of the Rev Edmund Nelson.

It will be the first time the baby clothes, on loan from Buckler's Hard, in Hampshire, have been on display in his home county of Norfolk.

Curator Hannah Bentley said: 'Nelson's private life is often commented upon with a bit of a nudge, nudge, wink, wink attitude to him having a wife and a mistress –Lady Emma Hamilton – but it's important to set the relationships within their historical context.

'Hopefully the exhibition will prompt visitors to reach their own conclusions about his behaviour.'

She said the exhibition told a real 'people story' in the mould of today's popular magazines.

'It is not just about big battles, it shows Nelson as a fallible man who made a mess of it,' she said.

Displays paint the picture of a man in many ways devoted to family, providing for his wife Fanny even after their separation and thinking of his parents on his deathbed, requesting to be buried next to them.

And his love of Horatia, whose birth gave legitimacy to his relationship with Lady Emma in his eyes, shines through.

However, to avoid offending the sensibilities of Georgian society, he could never officially acknowledge her as his child and came up with elaborate cover stories to explain how he had come to be responsible for his 'god daughter' or 'adopted daughter'.

The exhibition paints a tragic picture of Lady Emma as a hapless character who could have come off the pages of Voltaire's Candide.

Pregnant at 16 and used by a succession of men, her whirlwind romance with Nelson started in Naples in 1793; but the passion and glamour were quickly a memory after his death and her passion for alcohol and gambling consigned her to a debtor's prison.

Throughout the tangled story, Ms Bentley said Nelson still emerged as a likeable character.

She said: 'His background from a parsonage in Norfolk meant he did not care about society conventions. He was determined to do what made him happy.'

In a hint to the BBC, she said Nelson's story would make 'the perfect costume drama'.

The exhibition will run until November 2012. Normal museum admission prices apply.