There are many SCSI related modules. The mid and upper level modules are listed below:
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.
When upper level drivers are initialized and if there are no hosts active then the mid level will attempt to load a module called "scsi_hostadapter". An "alias" can then be used to associate "scsi_hostadapter" with the actual name of the lower level (adapter) driver. For example, a line like "alias scsi_hostadapter aic7xxx" in the /etc/modules.conf file would cause the aic7xxx module to be loaded (if there were no lower level drivers already active). 
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. 
There is a sequencing issue here if the root file system is on the SCSI device controlled by the lower level (adapter) driver to be loaded since it contains the /etc/modules.conf file. Also there is the issue of how the boot loader obtains the initial kernel image from a SCSI device (often from the (master) boot record). The latter is usually taken care of by the system's or adapter card's BIOS.
An example of using mkinitrd: assume the root partition is on a SCSI disk connected to a controller from Adaptec that requires the aic7xxx driver. After building a kernel with the aic7xxx driver specified as a module then load the image into the normal place (probably in the /boot directory). Next make sure a line like "alias scsi_hostadapter aic7xxx" is in the /etc/modules.conf file. Then from the /boot directory execute a line like mkinitrd /boot/vmlinuz-2.4.5 2.4.5 (this assumes lk 2.4.5 is being build). This should result in the file initrd-2.4.5.img being created. The /etc/lilo.conf should then have a section added looking something like this: