TLDR: cache invalidation is hard, even if you don't call it cache invalidation.
More seriously there are plenty of ways to make this less painful, eg, adding a "lastMessageTime" to the Room table, which would reduce the work to a simple intersection of RoomUser and Room. Facebook uses a more complicated system involving "graph" relations and a global ID space, but it's more or less the same thing.
Also, "normalized" is not a binary thing. There are several levels from 1NF (flat file, fully denorm) on through 3NF and BCNF (what the SQL books usually call "normalized") on up to 4th, 5th, and 6th normal forms, some of which are academic party tricks but sometimes see use in production.
To add to this: every serious production system tends toward a slurry of 2NF-3NF-BCNF models, mostly for performance reasons. The case of lastMessageTime is a simpler one, as it might drift out of date but generally you get false positives but never a false negative. Note that people complain about Facebook message jewels showing up when there's nothing new but seldom complain about not getting notified.
Another curious example is columnar databases. They are basically a 4NF storage layout with a veneer of 3NF syntax for ease of querying.
More seriously there are plenty of ways to make this less painful, eg, adding a "lastMessageTime" to the Room table, which would reduce the work to a simple intersection of RoomUser and Room. Facebook uses a more complicated system involving "graph" relations and a global ID space, but it's more or less the same thing.
Also, "normalized" is not a binary thing. There are several levels from 1NF (flat file, fully denorm) on through 3NF and BCNF (what the SQL books usually call "normalized") on up to 4th, 5th, and 6th normal forms, some of which are academic party tricks but sometimes see use in production.