Ovi Store is for commercially-developed apps (not necessarily paid for, but usually not open-source), maemo.org downloads is for community-developed apps (not paid for and almost always open-source). Maemo Select is a web page which showcases the best apps from the two previous places.

There is also the app manager built into the N900, which by default will show maemo.org "Extras" apps (pretty much the same as the maemo.org Downloads), but not show Ovi Store by default.

At least, that's my understanding. Corrections welcome. I've also created http://wiki.maemo.org/Downloading_apps