We *heart* our cat fountain
I have 5 indoor cats. After watching a few of them enjoy running tap water, as well as wanting multiple water stations available, we bought a fountain a few years ago.
They require good cleanings regularly (to avoid nasty fish tank effects) but it's worth it. Just make sure to prime the fountain after every clean so you dont damage the motor (add enough water before you plug back in). It's a no brainer but of course I did this the first time.
My cats were cautious at first, (typical cats!). But soon enough I found I had to replenish the fountain daily. SOmetimes I add ice cubes for added fun.
THe fountain is on the counter just to indulge my cats preference for vertical space. Plus it gives them privacy from my 2 toddlers.
One of my cats (this one is so weird) actually does a dance before he drinks. It's indescribable, really. It looks like he is cross crountry skiing? It's hilarious and I really need to get it on film but he is sneaky deaky about it.
In general I recommend fountains. Cats love the fresh running water, it's filtered and clean, and encourages further hydration.