Nowadays, katakana tends to be used for gairaigo and onomatopoeia, while hiragana tends to be used for native Japanese words. This is a slight simplification - more information is available here.
However, English loanwords weren't a major concern when they were originally created. What were the main motivations for creating the two kana (or possibly more?) rather than just one?
It isn't 100 percent clear, but the following is the “well-established” theory:
As noted in your other question, hiragana was originally called 女手{おんなで}. In the late Nara, early Heian periods, 万葉仮名{まんようがな} written in 草書体 (sosho style) was used for “unofficial” texts such as Japanese poems (和歌{わか}), etc. From this 万葉仮名, women in the imperial courts (宮中の女官達) developed the simplified writing style (女手 or what is now called “hiragana”). Originally, it was mainly used by women (hence the name 女手), but because of its ease of use compared to Chinese characters, men also began using it when writing. However, “official” writing still used Chinese characters and hiragana was used mainly among commoners (non-governmental people) and in poems and short stories, etc. Also, the 平 of 平仮名 also comes from its “simplicity” and “general use”. You can see it in words like 平易、平凡、平素, etc.
Now, katakana also originates from 万葉仮名, however it came into use first by Buddhist monks. Also, unlike hiragana, it was not used as a separate writing system from Chinese characters, but rather together with Chinese characters. In order to read those difficult Buddhist scriptures (お経), Buddhist monks would use katakana as a form of shorthand or annotation (called 訓点) as a supplement to the Chinese characters. In this way, katakana became used in official documents and for scholars, as Buddhism, 学問 and government was very closely related. Since it mainly was used by men, some people referred to Chinese characters and katakana as 男手 in contrast with hiragana (女手). Also, the 片 of 片仮名 comes from the fact that “pieces” of Chinese characters were taken to develop the writing system (and also there is implication that katakana is “temporary” in the sense that it is only to supplement the Chinese characters).
So to answer your question, the reason that the two exist is because they were created separately out of need at the time and they were originally used for two different purposes ― hiragana for a common language separate from the official writings (which used Chinese characters) and katakana as a supplement to official writing (and other text that used Chinese characters).
As I understand the Japanese wikipedia articles:
Hiragana and katakana were both developed from 万葉仮名【まんようがな】, itself a phonetic script using kanji for their readings. For hiragana, there was an intermediate stage, 草仮名【そうがな】, a cursive style of 万葉仮名, and the continued simplification of these characters lead to hiragana. Katakana, on the other hand, was initially developed by Buddhist monks for a specific purpose: to provide annotations to explain how to read Chinese texts in Japanese (和読【わどく】).
In some cases, both were used. For example, in 周易抄【しゅうえき】 (a text from 897年), 草仮名 were used for for annotations to do with meaning, whereas katakana were used to provide readings. Because katakana was primarily used early on by scholars as an aid to reading kanji, it gained a scholarly feel. Conversely hiragana became used for poetry and personal material like letters.
The actual word 平仮名【ひらがな】 appears from the 16th century onwards and in fact 平 here takes the meaning of 普通【ふつう】, to differentiate it from 片仮名【かたかな】。 There were also initially a number of variant forms of the kana, but these were eventually standardised in 1900. The alternative forms are called 変体仮名【へんたいがな】.
As to other forms:
合略仮名【ごうりゃくがな】 : characters made by combining two or more kana, for example より or こと, into a single unit. Used from the Edo period but use discontinued after 1900.
Potentially controversial answer
From what I remember being told (by a Japanese friend of mine), all of the Kana where invented by a single woman or a small group of women. I'm not sure on the validity of this, as it seems to be a hot topic with Japanese scholars.
As far as I'm aware, the Kana where both developed to aid in the study of Kanji. Since most kanji have multiple readings, it would make it easier for novice learners to remember the phonetic pronunciations of Kanji using a simple key.
PERSONAL OPINION SECTION STARTS HERE
I feel as though Hiragana uses shapes that are very similar to some elementary Kanji. Since Kunyomi readings seem to be the simpler ones to remember (your experience may differ), I'd say that shapes resembling the kanji where chosen for Hiragana for that purpose.
PERSONAL OPINION SECTION ENDS HERE
This might be why most systems use Katakan for onyomi readings and Hiragana for kunyomi readings - at least, that's how I view it.
Looking at most of the Hiragana characters, it's easy to see why women would have used them for writing, whereas Men would have used Katakana + Kanji (as has been pointed out by others)
The other thing to remember - as with most cultures - the skill of reading was usually taught to those who had the ability to pay to be taught (it was, usually seen as more of a privilege than a right, as it is these days). Thus, children who were born to families with the means to educate them, and the desire for them to reach high standing would pay for their children to study.
The consensus back then was that women had no place in the court, and the only place for someone who was educated was - you guessed it - at court. So, men would have be taught Kanji as part of their education in Chinese classics, Confucianism, Buddhism and history; whereas Women would have not have been taught such things.
I've no idea when, but at some point someone decided to invent a written script so that they could communicate - which is where the theory that it was a woman comes in. This implies that the person (or people) who invented the Kana where of high standing (otherwise they'd have to toil all day), and that they needed to communicate, in writing, with someone who was educated - probably a Husband or Father, due to the history of "Kidnapping" one's opponent's family members (again, this is speculation on my part).
this would also enable the Wives, Sisters and Mothers of the men of the court to educate eat other, and learn to read Kanji, This, in and of itself, would give rise to women being able to read and write - which gave rise to things like "The Tale of Genji".
I realise this answer is a little ramble-o-matic in nature, that I may have skated around the topic, and that I haven't quoted any sources (I'm at work at the minute, but I'll edit later on as and when I find evidence); so I apologise if this isn't of a high enough standard to answer the original question.
I think it is largely a myth that women invented hiragana. This myth likely comes from the fact that the script was called 女手 onnade or "women's hand", and katakana was called 男手 otokode or "men's hand". Hiragana was probably called 女手 not because it was invented by women, but because they used it exclusively to write — like in the Tale of Genji — because as you mentioned, they were not literate in kanji.
We see many documents written by men at the time that mix kanji, katakana, and hiragana, so hiragana was not only used by women, but women only knew how to use hiragana. The reason I say that women probably didn't invent hiragana is that were gradually simplified forms of kanji called man'yōgana, and women didn't know kanji, so how would they know how those kanji were pronounced?
Also, the Buddhist monk Kūkai is generally credited with making the kana. While we know this isn't true, and that the creation of hiragana was a gradual process, he greatly sped up the process of making kana by introducing the first purely phonetic script, Siddham (still used in some Buddhist temples in Japan today). By introducing Japan to the idea of completely phonetic characters that didn't have any separate meaning attached, the kanji that had been used phonetically (man'yōgana) could be simplified and used as purely phonetic characters without any meaning attached or alternative readings.
