It's delightful. Some of the spells and potions are kind of cheaty in that they just fill a need, clean something etc., but some are really fun. Your spellcasters can live mostly as normal sims with magic as a little side hobby, or they can have magic be their main focus in life. RoM comes with its own world Glimmerbrook which feels almost like part of the Magic Realm and is where the portal to Magic Realm is located. There are quite a few obvious and less obvious ways to tie in magic to other pursuits, for example gardening and potions are nice to pursue together.
I also like the fashions, which are if not really goth then goth-adjacent, and the build/buy which includes a lot of Art Nouveau curved and ornate forms.
I don't have vampires or mermaids so can't compare them.
I haven't had any trouble getting non-spellcasters to visit the Magic Realm. If you are not born a spellcaster, you need to go there on foot (through the portal) in the first place to begin the process of becoming one. Yes, families can visit. I don't remember if I've ever brought a toddler there, but children can visit for sure.
