Quick answer: The best pet water fountain is quiet, easy to disassemble for cleaning, uses replaceable filters, and is made from stainless steel or ceramic rather than plastic.
Plastic fountains are cheaper but develop scratches over time that harbor bacteria and can cause feline acne in cats. Stainless steel and ceramic are easier to sanitize, more durable, and don't leach chemicals. The price difference is worth it over the lifetime of the product.
Dog fountains need higher flow rates and larger water capacity. Cat fountains can be smaller and quieter. If you have both, a larger model with adjustable flow covers both — lower flow for cats, higher for dogs. Check that the bowl depth suits both animals.
Pump noise varies significantly between models. Check reviews specifically for noise levels if you're placing it near a sleeping area. Some models become louder as the water level drops — a low-water shutoff prevents pump damage and noise from running dry.
Spend a bit more on stainless steel or ceramic. The cleaning ease and hygiene difference is significant over the 2-3 year lifespan of most fountain units.