You don't need a true SATA card, unless you are concerned with drive bus performance. A simple PATA/SATA bridge adapter should work in your situation.
The primary reason for buying a SATA card is for RAID support, and on a G4 MDD, you only have 33MHz PCI sockets, not PCI-X, or PCIe, so your selection is horribly limited. You probably wouldn't even notice the difference, as the speed would be comparable to ATA/133, due to the bottleneck. It really isn't worth the time, or expense, to install a dedicated SATA-I card in this system.
You would probably do better to buy a PATA SSD, or a very fast CF card and a CF/PATA adapter (which is in essence, a pin converter), as you would see a greater impact from the NAND operational speed, than from the bus speed change from ATA/100 to SATA on PCI.
You could find a SCSI-II or SCSI-III card from the era, and put a high-speed (10K or 15K RPM) drive in the system, which would be able to give you a nice boost, along with a with a faster drive bus speed, but this again, is an expense that you likely do not need, unless you see future use of the SCSI expansion.
You can find SCSI RAID chassis on the cheap now, and SCSI accessories, such as scanners, printers, and the like, and LTO/LTO2 tape drives (use with IOSCSITAPE), so that might be a more reasonable avenue to follow, if you want a better drive host that you can use as a boot device.
Converter boards (SATA/PATA or CF/PATA) sell at around £5. It would be easier, and much less expensive to buy a Dual-G5--a system twice as powerful, with SAT on the mainboard, plus three PCI-X slots--than to add a bootable SATA PCI-33 card to the MDD.
P.S. CF cards are already PATA devices, so a pin-converter from CF/PATA will allow you to use them as standard drives, without some of the worries over boot compatibility that some SATA/PATA adapters may present on G4 Macs.