Wow. Difficult question, so here’s a guide based on personal experience.
I strongly recommend that you go to a tent-dealer and get inside a few tents before you buy. And get them to put a mattress in so you can gauge sizes more easily.
1-man tent: Good for one person with very little luggage indeed. Prepare to feel snuggly/claustrophobic, but the small space warms up easily. Look for one with a porch if you plan to take a backpack. 1-man tents are generally too small to sit upright for any long periods so if you plan to stay under cover all day, get used to laying down.
1.5-man tent: Good for one person with a backpack, as long as you don’t mind curling up around it. Also great for impromptu nights with an Oompa-Loompa, as long as they don’t bring any luggage.
2-man tent: Good for 2 people with no luggage. Look for one with a porch if either of you have a backpack or luggage. Look for height if you plan to sit up, since they can often slope in quite a lot, meaning that one of you needs to shove over if the other sits up.
3-man tent: Good for 2 people with a backpack or 3 (very comfortable with each other) people with no luggage. I’d consider this the best size for 2 people to share for more than a couple of nights.
4-man tent: Good for 2 people when ‘social’ camping with cookers and food. The extra space means you can find things without having to lift mattresses. Yes, you can probably squeeze 4 people in, but it’d be a sleeping-only space.
6-man tent: Now you’re looking at something suitable for two couples or a family of 4 doing minimalist camping without too much luggage. Generally the tent will be starting to get big and heavy to carry now, so bear that in mind. There are so many configurations of 6-man tents now that it’s worth thinking about how you will use the porch space, and how you’d like the sleeping arrangements. Tents of this size often have room dividers in them, which can be a bonus.
8+ man tents: A family of 4-6 should find a tent this large ample for a holiday. Consider porch space and sleeping arrangements… there may well be 6 of you, but who doesn’t want to sleep next to who?
Bigger tents scare me, so you’re on your own.