3rd party photo management software for MAC desktop

Sure. Quicklook is built into your Finder. Click on an image and tap the spacebar. Move through a folder using the arrow keys.

In terms of apps: Apollo One highly thought of

https://apps.apple.com/ie/app/apolloone-photo-video-viewer/id1044484672?mt=12

as is Adobe's Bridge

The only weakness with that approach is that really you are organising files, not photographs. What do you do with an image that fits two categories, which folder does it go in? Or do you duplicate the file and waste space. One thing about data base app is that they allow multiple categorisations of the same file, and so your photo maybe in lots of categories but you're still only using one file. In other words they allow for organising your photographs, rather than the file.

I want to load & store my photos onto my Mac's hard drive for easy access anytime. I want to categorize them into folders or albums myself for easy viewing. I want to then be able to access them for transferring to other devices & create my own backups for later retrieval.

From that description, and with a 10k library, check out Mylio:

https://mylio.com/

There's a free and a paid version with extra options.

maybe all I need is some type of a viewer app (rather than full blown program) since I have all my photos already within folders (exported from libraries) sorted by year, categories, etc. Wondering if I could just download some type of "browser" to see them full screen & scroll through multiple photos in a folder at one time?

I use QuickLook to browse images and movies in Finder folders (select the first one or just the desired files, press Space bar and press down/up arrow keys to browse, optionally switch to full screen mode). Or browse with GraphicConverter (optionally search via Keywords and edit metadata).

Well, if the app store blurb can be trusted (and the 4 star rating) Yer_Man's suggestion of Apollo One seems like the winner for you. I do always wonder about those mysterious "in-app purchases," but it does start out free, so you get to try it. It apparently handles quite a range of Exif metadata.

If you find what you want, let us know!

You do know that using iCloud Library is strictly optional, right? It's not mandatory.

Photos will do everything you mentioned except for the curating. You can't get away from it. But all of the other things you want to do it will do. Also it you want a photo in more than one folder/album you don't have to duplicate the file thus doubling storage requirements for that photo. It's done by database and pointers to the original photo.

Just some food for thought.

FO you scroll down on the App Store page you can see that Apollo One has "In App Purchases" and by clicking on the disclosure triangle you can see the details

For details on what each tier represents in capability you can read here:

https://www.apollooneapp.com/faq

I know nothing about Phiewer, I'm afraid. In your position I would google for reviews on both apps and the download trials.

CyberShop wrote: ... I want to load & store my photos onto my Mac's hard drive for easy access anytime. I want to categorize them into folders or albums myself for easy viewing. I want to then be able to access them for transferring to other devices & create my own backups for later retrieval.

In your case, I would be concerned with "a photo management database that allows you to browse & organize photos stored on your computer." That, after all, is something Apple's Photos can do. But what happens when you "categorize them into folders or albums myself?" We know that keeping track of files can be very tricky when both you and your "photo management database" are both organizing them. We don't normally do that in Photos, because we end up spending more time fixing missing links than working with pictures. But there are some systems that are way better at that-- you'll want to give it a dynamic test before investing a great deal of work...

Quicklook is very powerful-- you can expand to full screen and even mark up the picture.

But Yer_Man's point is sharply made-- a database program like Photos always keeps your original file intact and unmodified, while it saves edits as extra data. You can always change your mind later about the editing and cropping of the pictures. Also, the database can be used to refer to pictures that are organized by several different kinds of parameters.

And perhaps the most powerful advantage of a database like Photos the ability to search. Photos, for instance, lets you use keywords, captions, faces, locations, and titles to help search though your pictures for that one image that you can't remember quite when you took it. Organizing pictures using folders doesn't let you do easy and fast searches, and it doesn't encourage you use if multiple organizing