The biggest consideration is do you want open-pollinated seeds, or are hybrid seed alright?
Hybrids will perform much better in marginal climates. For sure.
I myself, despite living in a cool mountain climate, have a strong preference for open-pollinated seeds, as I enjoy seed saving. The only hybrid I grow is eggplant, because OP eggplant really dont produce adequately here.
For OP melons, my favorite, hands down, is Northern Arizona. It is very rare, I belive the only supplier is Bountiful Gardens. I love this melon so so so much.
If you are okay with hybrids, your options expand tremendously. Ambrosia is a classic. I also like
Honey Yellow from Johnnys Seeds. In general, the cantaloupe type melons will be much easier to ripen in cool climates. Any of the exotic melons, or honeydew melons, need much more heat, IME.
Watermelons have never developed good sweetness for me, despite trying many varietals.
Baker Creek seeds prodominantly grow varietals that are adapted to warm/hot climates. None of their melons have every worked well for me. Great seed company, just not for cool adapted varieties.
hope that helps, good luck!