I was curious so I looked into this a bit more, and here's what I found.
The Start Manager of IM: OS Utilities along with IM: Devices SCSI Manager section has some relevant information on this. My understanding is: when a SCSI device is detected in the SCSI chain by SCSI Manager, it issues a SCSI Read of sector 0. The first sector contains the Driver Descriptor Record, which contains sector size of the device, location & length of the device driver(s), etc. SCSI Manager loads the driver into RAM, and opens the driver. Subsequent access to the device is handled through that device's device driver. The SCSI Manager then uses the device driver to load the partition map, and adds each partition to the Drive Queue. After all slots, SCSI ID's, partitions, etc. have been enumerated, the ROM then tries to figure out what Drive (from its Drive Queue, which contains pointers back to the driver used for accessing it), first checking the floppy, checking the selected Startup Disk stored in PRAM, and eventually enumerating the drive queue looking for a valid HFS signature and valid boot blocks that point to the system file.
Multiple device drivers are allowed, as the Driver Descriptor Record can chain to multiple entries, but only one is used. Frequently disks will have both a pre-SCSI Manager 4.3 driver and a 4.3 driver. The Apple Partition Map has partition types for the drivers, but the partition map is not referenced in the discovery of the driver AFAIK since it isn't read until after the driver is loaded. The partition entries are to call out the area as used by the driver so it doesn't appear free and get overwritten, but the DDR is the authoritative location of the driver(s) from the ROM's perspective.
The Apple CD-ROM Handbook (1993) indicates this process will be followed for CD-ROMs as well. At the time that book was published, it explicitly states booting from CD-ROMs was not yet supported, but it did note that the Driver Descriptor Record along with the HFS boot blocks needed to be zeroed out because existing ROMs would attempt to honor them.
As for 3rd party CD-ROMs, I suspect 512 byte sector support might be required in order to boot from earlier ROMs. Typical CD-ROM sector size is 2352 bytes, but hard disks use 512 byte sectors, and existing ROMs only knew about hard disks.
As best I can tell, the DDR is requested regardless of the SCSI Peripheral Device Type (returned as part of an Inquiry command). I guess this would mean if you had a non-disk SCSI device (scanner, ethernet, etc.) that could return a DDR and driver through SCSI Reads, you could get a Mac ROM to load any old device driver from the device on boot. That's speculation, but if someone wanted to make random cool SCSI peripherals, it might be worth exploring further.
But, that's all for booting. Almost any CD-ROM should work for mounting discs from within the Finder, assuming proper drivers are available.