Regular readers here know I am a real fan of the Royals. If you search this forum you will find many posts by me and others that, along with reading TUG reviews, will give you information.
The Royals' villas are all laid out the same - except for decor and some upgrades (e.g. countertops and appliances) over the years - you'll have pretty much the same layout and space in all of them. In all cases the maintenance and service is excellent; the age of the older ones (e.g. about 25 years for the Mayan) is not relevant.
The main decision is whether you want to be at the Tris (Mayan, Caribbean, Islander), Sands, Club Internacional, or Haciendas. There is a different feel in each case - the Tris (and it doesn't really matter which of the three) seem to have a warmer feel than the Sands, to some of us. Some have described the Sands as more sterile, or more of a hotel feel. Maybe a lot of that has to do with how long people have been going to each one, how many "regulars" there are, and so forth. The Haciendas is in Playa del Carmen and you'd need to read posts or reviews for more info.
In all cases, you have nice pools and beach walking. In all cases you can take a bus or van to the ruins.
If you look at the pictures on royalresorts.com or mexicondo.com or even the satellite pics on google maps, you'll see the resort layouts and the pools. You have more pools to choose from at the Tris, and the pools at the Caribbean are really huge. All the Tri-Royal pools are curvy and landscaped, which provides more of a variety of areas to sit, nap, have conversations. One of the pools at the Sands is huge - but straight-sided, not much landscaping, and the chairs are in multiple rows like a cruise ship. The other is curvy and more like the one at the Islander. The Haciendas has two straight sided pools. The Sands and the Haciendas have lap pools.
The Royals pay and treat their staff well, and we have had the same waiters, maids, etc for many years. They are always friendly and provide great service - but not intrusive.
The water is definitely not a problem - not at the Royals or at any of the major hotels, resorts, or restaurants. I've been drinking ice-water there for years, and eating salads, fruit, etc, with no problems. You are correct that there is a water purification system. At a smaller more local place, you might not have the high cleanliness standards and you have to be cautious.
I suggest searching this site and also the reviews, and of course post or PM with questions.