Most "best house" lists rank by vibe, which is how you end up being told Breezehome is cosy and then having nowhere to put your daedric collection. I care about storage I won't lose things in, crafting stations within a few steps of each other, a location that doesn't waste my life on loading screens, and wall space for the loot I actually want to look at. Ranked by that, the order shuffles a lot.
The serious workshops
Hjerim (Windhelm) is my pick for an actual base of operations. Five thousand gold and the city's a misery to navigate, but once it's furnished you get every crafting station except an arcane enchanter under one roof, plus the only weapon and shield display plaques worth bragging about. The dragon mound out front is a nice touch nobody asked for. The catch is Windhelm itself: Stormcloak-flavoured, slow to load, and you have to clean out a murder scene before you can buy the place.
Proudspire Manor (Solitude) is the rival, and it's close. It's the most expensive vanilla house at 25,000 gold (8,000 in upgrades on top), but you're paying for the address. Solitude is the capital, the Bards College and Blue Palace are right there, and the alchemy/enchanting setup upstairs is tidy. No smithing forge inside, though, which for a 25k house is a genuine slap. You walk to the city forge like a peasant.
The Hearthfire houses change the whole conversation. Build all three wings and you've got every station, mannequins, weapon racks, and an apiary or fish hatchery if you want to play homesteader. Lakeview Manor (Falkreath) is the one I keep coming back to. Central-ish, pretty, good garden space, and close enough to Riverwood and Whiterun that it doesn't feel exiled. Heljarchen Hall (the Pale) has the best view in the game and a grain mill, but it's cold and out of the way. Windstad Manor (Hjaalmarch) sits in a swamp next to a Hagraven, which tells you who designed the local council.
The honest small ones
Breezehome (Whiterun) is the people's champion and I get it. Five thousand gold, sometimes free off a quest, and it's thirty seconds from Warmaiden's forge, the Skyforge, Arcadia's alchemy and the marketplace. It has barely any storage and only an alchemy lab plus a couple of mannequins after upgrades, but its location does so much work that I've finished entire playthroughs without buying anything bigger. Location is a feature.
Honeyeve Hall (Riften), sorry, Honeyside, is the underrated one. Cheap, has a back door straight onto the lake for fast travel and fishing, a small garden, and an alchemy/enchanting corner. Riften's a sewer politically but the house is calm.
Vlindrel Hall (Markarth) is all stone and quiet, decent display space, and badly placed for anything you actually do. I buy it for completion and never sleep there.
So what's it for, really
If I want one base that does everything, it's Hjerim or a fully-built Lakeview. If I want to drop loot and get back to a dungeon in two minutes, it's Breezehome every single time, and I've stopped feeling guilty about that. The fancy houses are display cabinets; Breezehome is a working desk.
A couple of things worth knowing before you commit: mannequins in Skyrim are notoriously buggy, gear clones itself, they wander, occasionally one strolls outside, so don't trust them with anything unique you can't reacquire. And weapon racks drop their contents if you breathe on them wrong. Test with junk first. The UESP house pages (https://en.uesp.net/wiki/Skyrim:Houses) have the exact upgrade costs per wing if you're budgeting a build.
For the homestead crowd, we've got a build-share going at https://elderscrollsforum.org/vi/t/hearthfire-builds if you want to steal a floor plan.
Here's my actual question: when the dust settles and you're a hundred hours in, which house do you genuinely end up storing your loot and sleeping in. The trophy mansion you spent 25k on, or the one that's closest to a forge?