Cost-effective storage of solar & wind generated power for home use
Energy storage is big business...

There are many ways to store energy.

Your idea of the water tanks is a reasonable one, and not that hare brained.  It's exactly what a number of hydro-electric plants do.  They pump water to a high reservoir during slack times, and then use the descending water to generate power during high demand times.

Other ways are, as you say, batteries (expensive) or big capacitors (even more expensive).

If you have the space then a flywheel is also a possibility.

You crank up the speed of a huge heavy wheel when you have the power, then use its momentum to generate power at other times.  This is sometimes used for big UPS systems for server farms.  I know that, for instance, HSBC have a massive one underground to act as one (of many) of their UPS systems to protect the bank computers.

This takes a huge amount of space, and you need good bearings or you'll lose all your power through friction.  A vacuum is also handy to reduce air resistance.

Other mechanical ways could be to use clockwork.  A motor winds a spring up during times of excess power, and then it unwinds generating power when you need it.  Or you could use elastic, or springs, to store the energy - stretch them out using your excess power, then let them collapse back while generating power.

There are many many ways - it all depends on how much space you have and how much complexity you want to get in to.