They are just normal sims which has pale skin and glowing eyes, they have longer lifespan (x5 of normal) and have a few extra special abilities. They also don't like sun obviously but it does not kill them. Overexposure to sun only causes their needs to decrease more rapidly (This can kill vampires because of low thirst but sun does not kill them directly).
They don't really have that much "secret" thing going on either, they just exist like other sims. And I don't think anyone cares about who feeds on what in this game. The only thing is that when you are a celebrity and bite someone in public, you will get "publicly disgraced". And that is more of a celebrity system thing rather than vampires thing. You can get disgraced by a lot of things, so this is not something that is special to vampires only.
With Supernatural, you get a few social interactions like accuse of being a vampire, deny being a vampire etc but they only affect relationship of the two that are interacting with each other, like all other social interactions. So there is no social disadvantage of being a vampire.
And also I guess you can also get arrested when you "raid" rabbitholes for blood, which I believe only gives you plasma juice, which you can get for free using the refrigerator anyway. Maybe there are other outcomes of this because I did this like maybe 3 times since 2011.
I am personally OK with vampires as they are in The Sims 3, but I don't think they are better than TS4 ones (I never played with TS4 vampires). I usually create them as hybrids with other life states (mostly witch) using NRaas Hybrid and MasterController to add a little bit more depth to them.
Check out this for more info about TS3 vampires: https://www.thesimswiki.com/wiki/Vampire_(The_Sims_3)
There are also mods like this which adds more stuff for vampires: https://modthesims.info/d/648599/enhanced-vampires.html