The thing with Portal 2 and TF2 is that, with both games being multiplayer (the workshop is not related to Portal 2's single player) there is no way to mod in new hats and stuff for free. So when modders make new stuff, it has to be approved by Valve to be put in the game. There isn't a TF2Nexus type of site. At least that's how I understood it.
This isn't the case with Skyrim. Skyim mods don't have to be officially approved since it's not a multiplayer game, everyone is free to install whatever mods they like and get them from whichever site they like. So I don't see how Bethesda could charge people for mods, on PC at least.
And then there's the testing this as I mentioned in my last post. A TF2 hat is easy to test for bugs, a large gameplay overhaul isn't. If Bethesda would charge for a mod, they'd have to be absolutely sure that this mod doesn't have any big issues. And they don't have the time to anolyze complex mods to see if they're okay.
Actually, you've been able to mod Valve's games (both single and multiplayer) since forever. It's just that in order for other players to see your mods, they need to have them too. There's the method you said, which is relatively recent. Or the old way: Have the mods on the server itself, and they download automatically when people join. That's how things like http://cloud.steampowered.com/ugc/596951791367759638/9921C3D8A72DE02088595FDE46A04245B36048B6/ exist. These tend to be server specific though. But there have always been unofficial mod sites for Valve's games. Some game-specific, some general. I certainly didn't get http://img535.imageshack.us/img535/1888/hl2pepsi.jpg from Steam. The only problem with unofficial custom content, is that some servers disable it entirely due to cheating (making walls transparent via texture edit). But that's a non-issue in a single player game. Both systems can co-exist peacefully.
But we don't know the details of how it will be implemented in Skyrim. That's the only thing that may be a concern. But unless Bethesda uses some dirty legal tactics (which the fans will of course obey...), then they can't control what you physically do with your mod files, since they'd be on your hard drive. I doubt they'd auto-upload everything to Steam, since then it would flood it with unfinished mods. So I'm sure it will be optional.