I do not see the Steam Workshop handling conflicts between mods very well but Bethesda has been working with TES games for some time now and they watch the mods that get created and used so who knows maybe it will work.
I think it would be cool to pick mods at work or where ever from my phone then get home and play.
I love mods but it would be nice not to deal with conflicts. I really don't see this happening but I can dream.