We don't have a water feature so I cannot advise but am happy to opinionate.
I love the sound of running water but dislike stagnant water and algae/frog spawn.
At the shows the nice ones are like colourful drain pipes in a zig zag along a wall, or sheets of water coming out of a linear spouty thing.
The old fashioned moss/stone lion head on a wall with water coming out into a shell shaped bowl, which birds can sip from, is always nice.
The metal spheres are cheap and child safe.
I want a large buddha with a lily pond.
However, water running down stair rails, with fountains, as at the Alhambra are my favourite water garden feature of all time.
The dolphin fountain at regents park (so long and thanks for all the fish) is also nice.
The stuff at Chelsea flower show is largely expensive naff tat.
At Hampton Court the water in the one show bath garden was in a series of rectangles and had stuff put into it to make it dark and reflective.
The solar water features are nice.