The SATA drive port can support any drive that physically fits! As an example: Seagate Exos HDD ST28000NM000C 28TB 7.2K RPM SATA 6Gb/s frankly, this is a bit too large for a desktop, it's more suited for a NAS server. I would stick with nothing larger than a 4TB.
Most people use SSD's over HDD's these days as they do offer quicker access and don't require as much maintenance. I would stick with Samsung 2.5" SSD's they've a good track record.
As far as a Fusion Drive that was a stop gap solution Apple came up with to get a bit more performance with HDD's by putting a SSD with it as a caching drive. At the time SSD's were still quite expensive and not available in the larger sizes unlike today. I don't recommend Fusion Drives as more often you lose your stuff when the SSD fails even though the HDD was OK.
Dual drives - The ideal setup is to install a custom Apple Blade SSD as the primary drive (boot, OS, Apps) with a fair amount of free space so the OS can setup Virtual RAM & OS Caching and under Windows via BootCamp Paging. I would go with either a 1 or 2 TB blade SSD here. Depending on the Apps you are using that may have their own caching or scratch space needs. I would limit the primary drive for current workpieces as you don't want to over do it as you want the free space on the drive. Then the second drive is your data drive for your media (music, pics, or vids) as well as your work.
When you start getting into larger storage there's a point you really want to divorce the deep storage to a NAS server as it can offer even deep storage, speed as well deeper protection of data loss. This is were RAID comes to play setting up drive spanning to increase speed and using one drive as a CRC drive make sure you don't loose data. It also allows a second system access to the data.
35 
339 
975 
