I've had a nose screw in since 2005 and I love mine. I took it out for a year or two after I had kids, but missed it enough to put it back in. It was a very painless piercing, except for the tear-producing reflex of messing with the nose. But the tears were NOT from pain and I think it healed faster than any of my other piercings, too.
They are definitely mainstream enough now to not cause a flinch. My favored nose screw is gold with a very small diamond on the end, and it is very elegant and dainty. You can go quite dramatic with your jewelry choices, but as a conservative, Christian, homeschooling mom with very young children, I am better suited less gaudy jewelry than more
. But if a piercing isn't commented on in my social circles, it's pretty much blasé, at this point!