There are many SCSI related modules. The mid and upper level modules are listed below:
scsi_mod.o
sd_mod.o
sr_mod.o
st.o
sg.o
Notice that the first 3 have "_mod" appended to their normal driver names. Lower level drivers tend to use the name (or an abbreviation) of the HBA's manufacturer (e.g. advansys) plus optionally the chip number of the major controller chip (e.g. sym53c8xx for symbios controllers based on the NCR 53c8?? family of chips).
All SCSI modules depend on the mid level. This means if the SCSI mid level is not built into the kernel and if scsi_mod.o has not already been loaded then a command like modprobe st will cause the scsi_mod.o module to be loaded. There could well be other dependencies, for example modprobe sr_mod will also cause the cdrom module to be loaded if it hasn't been already. Also if the SCSI mid level is a module, then all other SCSI subsystem drivers must be modules (this is enforced by the kernel build configuration tools).
Modules can be loaded with the modprobe <module_name> command which will try to load any modules that the nominated <module_name> depends on. Also <module_name> does not need the trailing ".o" extension which is assumed if not given. The 'insmod <module_name>' command will also try and load <module_name> but without first loading modules it depends on. Rules for how modules can cause other modules to be loaded (with appropriate parameters appended) are usually placed in the file /etc/modules.conf. [Note that in earlier Linux kernels this file was often called /etc/conf.modules.] For further information about the format of this file try man modules.conf.
There is a special relationship between the module parameter "scsi_hostadapter" and the initrd file system. For more information see man initrd and man mkinitrd. For example, after the initial configuration of recent RedHat distributions on my system the /etc/modules.conf file contains the line: "alias scsi_hostadapter advansys". My system boots from a SCSI disk connected to an advansys controller (thanks the BIOS) and the root file system is also on the same SCSI disk.