Why was monorail offered the way it was?
Why design the core monorail hardware this way?



This is hard to answer without a degree of speculation, but I assume that the core reason behind the decision to design the monorail this way likely came down to cost and engineering challenges. TLG wanted to allow for a monorail train to be able to navigate inclines effectively. This required:


A cog system
An additional degree of freedom between cars compared to trains (vertical movement as well as lateral)
An additional degree of freedom between wheel sets and cars
More specific power to weight requirements to maintain speed on inclines


Ultimately, TLG landed on the fairly rigid system of a central motor and couplings that create the necessary degrees of freedom with a car on either side. This likely required the smallest number of new parts and showed implicitly that the monorail motor isn't designed and tested to work well pulling more than two cars.

Why offer only large sets and not just a basic oval?

The core features of the monorail system that differentiate it from the regular trains of the era became:


Inclined and elevated track
Stopping at particular track segments rather than being controlled remotely


Given these features, it makes sense that any complete monorail set would have at minimum:


Elevated track including ramp sections
At least 2 monoswitches. Technically, you could just have one, but that would really limit play value since the train wouldn't have any destination other than its starting point
A point of interest at the monoswitches for play value


Unless these features are included, the set might as well just be a regular train set. While you are correct that the later sets were more complicated, 6990 was basically just an oval of track with a pair of ramps and two monoswitches. I believe it included just two straight track sections in addition to these.

Given the inflexibility of the overall system (e.g. you can't just add one more car), TLG decided to include both cars in each complete monorail set and not offer any as add-on sets.

Comparison to 9V train sets

While there were many small train-themed sets in this time period, I'm not aware of low-cost starter sets. You could certainly buy a pack of 9V curved rails and a motor and work your way up from there, but the three complete sets released between 1989 and 1994 were actually fairly expensive:


1991 Metroliner (4558) - $149
1991 Load and Haul Railroad (4563) - $120
1994 Freight Rail Runner (4564) - $134


Compare these to the complete monorail sets of the same period:


1989 Monorail Transport System (6990) - $155
1994 Monorail Transport Base (6991) - $178
1990 Airport Shuttle (6399) - $140


The difference in price between the cheapest monorail and train set from the era is only $20.

Why no add-on sets?


  When I was a kid, I loved the Airport Shuttle, but I was unable to buy
  an extra station or train, because there simply wasn't any.


While there weren't sets that included additional cars in the same way as regular trains, the monorail sets were generally part of larger themes that had additional sets available and were marketed together. For example, there were aircraft of different sizes and an airport marketed alongside Airport Shuttle:



The Futuron theme had many sets available as well, including another base:



Just in case my answer didn't make this clear, I really like the monorail theme. However, while being great collectors items, I believe that the monorail theme is generally considered to be a business failure. Offering two completely separate train systems at the same time with slightly different features ultimately didn't pan out well for a lot of the reasons that you cited.