I would want a room that is twice the size of the average bedroom. You can always make a cozy space in the corner, but you can't make a room bigger. I am using my elder daughter's old bedroom. Our house was built in 1975, so the bedrooms are quite small. I could use a larger room, although I am thrilled that I have a sewing room at all! for over 16 years I didn't have one. I would not be concerned about upstairs or downstairs as much as which side of the house I'm on. As much as I love the sun shining in my room, I have my sewing machine under the south facing window. When the sun is shining, I am nearly blinded. As far as guests seeing a mess, you can always shut the door
I am thrilled for you! Let us know what you decide on, please.