I strongly ditto John's suggestion of "The DAM Book".
However, some tidbits beyond, and based on, Peter Krogh's book:
First note that I use Lightroom, where large catalogs don't slow down much. At first I had 3 catalogs, but when that got to be too much of a PITA I narrowed it down to one. Then again later I split it into 2 catalogs: Family/Personal and Main. I separated out the ones that ONLY family or myself might be interested in - plus I didn't want to see the constant painful reminders of close loved ones that died.. So 2 makes more sense,
to me. It will be different for everybody though.
As far as color labels and stars, I pretty much use Peter Krogh's suggestions to a 'T'.. Where starting at 1 star, there are more photos, then 2 stars should have ~10x less photos than 1 star, and 3 stars should have 10x less than the 2 stars, etc.. I don't think I have any that are more than 3 stars at this point. Then for color labels I use red for 'Neutral', yellow for 'Outtakes', and green for 'Outakes of Outakes' (basically 1 step away from flat out deleting, but didn't want to for whatever reason). I think Peter Krogh calls that last one trash, but in LR we have flags for that. Then I even combine the stars and color labels so, for example, green with 3 stars would mean 'the best of trashy photos'.
Hopefully Capture One will allow naming of the color labels at some point, in order to make associating color with the meaning easier.
I think what you're going through though, is what every 'photographer' needs to go through for themselves.. To feel their way around finding what might work best, for them. I don't think
anyone has had any shortcuts around this hard part. Of course, part of that is finding out what others might be doing - in order to get some ideas at this stage.
But yeah.. Definitely read Peter Krogh's book.