I think "ideal" is a tricky question to answer, however in my opinion you want to balance the complexity of the URL against a good structure whilst also not including anything that doesn't need to be there.
From your example, I would personally go for something similar to your option 3, as such:
foo.com/products/transformers
foo.com/products/transformers/megatron
The reason being that the word "catalogue" is pretty much irrelevant as far as visitors and Google/etc are concerned. Swapping that for "products" and removing the additional segment shortens the URL a little, whilst still maintaining the key structure (/products/[catalogue]/[product]).
[catalogue] and [product] are also your important keywords from an SEO point of view, so the closer you can get them to the start of the URL the higher priority they will be deemed.
This is also all highly dependant on your CMS or development platform, it assumes you have complete control over the URL structure and nothing is being insisted upon you by that platform.