I don't think there is much of a difference between the composite cable (with the red and white connectors for audio and the yellow one for video) and the RGB scart. As far as I know, the latter simply converts the input into a different format but without actually changing its quality. Someone please correct me if I'm wrong.
EDIT: Well, Suits says otherwise. Guess I'm getting confused with all the different scarts, then...
The best option would probably be the component cable (with the same red and white connectors for audio but with three separate connectors for video: red, blue and green). That's what I use and the difference is noticeable. It won't put out anything even remotely resembling HD resolution but it's a significantly cleaner and sharper picture when compared with the default composite stuff.
That's the one you want, provided your TV is compatible with it:
The good news is that the PS3 and PS2 component cables are completely interchangeable which means they remain pretty affordable even now. I would suggest sticking with the official product if you can.
Plenty of greatness to be experienced this way. Do it.