It used to be that the only reason you would want a sound card was for playing games. That's no longer true; Windows itself uses sound to get your attention and convey information when it's important, and many multimedia programs require sound in the same way television does.
Just about any sound card is compatible with any computer, but software compatibility is very finicky. Most sound-enabled software uses Sound Blaster drivers, so if the sound card you want is Sound Blaster compatible, you're in good shape. If you are also installing a CD-ROM at the same time, you will need to make sure that the sound card has an interface that matches the CD-ROM, or that there is a separate interface available. For example, if the sound card sports a Panasonic and a Sony interface, but the drive is IDE, they won't work together. In that case, however, you may be able to plug the CD drive into your existing IDE hard drive controller.
Once you have your sound card, there are several settings on it that you need to check to make sure nothing on the computer is already using them. The I/O address, a number somewhere in the hundreds, is usually not a problem because there are so many settings available. An available DMA channel, a small number ranging from 0-3 or 0-7, may seems hard to find, but there really aren't many devices at all that use them. The number one item to watch is the IRQ. Computers only have 15 IRQ's total, and just about everything needs one, including the video display, keyboard, modem, mouse, hard drive, floppy drive, printer, etc. Sound cards generally use IRQ 7 or 5. The problem is that printers use these same numbers. Check your computer's and your sound card's documentation to find out what is available.
One very important caveat to watch out for when installing a CD-ROM drive is that if you have a '386' computer, don't get a 4X speed or faster drive. The computer won't be able to keep up, and you will have problems.
One final note: if you have problems playing audio CD's after installing a new sound card and/or CD drive, make sure you have the audio cable plugged in properly.