There's a whole range of ahwas, from really tiny, dirty ones that have the same thirty men sitting there every day to really nice one's like Fishawi's in Khan al-Khalili (the main bazaar) that is always full of businessmen, Egyptian tourists and foreigners (the pictures below are from Fishawi's). My favorite ahwa is definitely Ahwa Zahara-Bustan, which is a small ahwa in an alley off one of the main roads downtown. It is an ahwa in the middle of the spectrum: you see both women and foreigners there, but its in a sorta dingy location and the prices are phenomenal (15 cents for a tea/coffee, 50 cents for a shisha pipe). This ahwa used to be famous because it was the intellectual haunt of Cairo--apparently Nasser and the Free Officers made plans for their famous revolution in 1952 here.
The ahwa is just an amazing place, a place to hang out with your friends for hours talking, playing awesome games and just watching people all around you. It's too bad that we don't have a similar type of social institution in the States. Its place is sorta taken up by the bar scene, but that is a little too hectic sometimes, and doesn't allow for chill and conversation-centered socializing. If you ever, EVER, come to the Arab world (or Turkey), I say that you MUST go sit in an ahwa (not a trendy one, but a regular one), and you will probably become obsessed with it just like me.