There is an excellent answer to this question in a post by barrygordon over on the unRAID Server Community:
To make it simple, if the host-controller side is a SAS connector (SFF-8470) and the target side is SATA drives then you must always use a SAS to SATA Forward Breakout Cable. If The Motherboard/host-Controller side are SATA connectors and the backplane is a SAS connector then you must always use a SAS to SATA Reverse Breakout Cable. For SATA to SATA you just use a "SATA" cable as there is only one type, although they do come in different lengths. For SAS to SAS connections there is also just a single cable type.
See also this excellent answer to How exactly does a SAS SFF-8087 breakout cable work? + RAID/connection questions.
Also, as Alex, said in comments:
Some confusion appears to stem from the fact that backplanes exist that have individual SAS/SATA ports such as the SuperMicro SAS743TQ. In this case, you do need to use forward breakout (e.g. 1x 8087 -> 4x SATA/SAS) - despite the fact that you are connecting those forward facing cables to the backplane.
So, to summarise:
Cable needed SATA drives/backplane SAS backplane
--------------------- --------------------- ----------------
SAS host/motherboard Forward breakout Mini SAS cable
SATA host/motherboard SATA cables Reverse breakout