Japanese writing: how do you know what characters to use?

Novice student of the Japanese language, here.

There seems to be a lot of redundancy in the writing systems used for Japanese. Disregarding the Chinese loanword characters for the moment, the hiragana and katakana systems have a lot of overlap in the sounds they transcribe; I’m not even sure if there are any syllables you can write in one but not the other.
So, when writing words I’ve only heard pronounced, how do I know which to use? How do the Japanese? Do they just memorise it?
Why are there even two of them in the first place?