I would actually abstract both 'types' of waste into one type of waste that counts as a solid object with it's own category called filth. When mixed with water it becomes liquid sewage that renders the water undrinkable.
Over time filth rots away into soil on rock or into soil nutrients if placed on soil. I do not think we have to simulate water corruption except by direct contact of water with filth as I do not think that waste is actually capable of corrupting anything through the equivilant of one Z-Level of soil.
I think I could possibly get behind that abstraction of both types of waste to one, but not the water corruption thing.
Fecal waste IRL is indeed capable of corrupting drinking water through what I would imagine is at least one tile of soil. Disease causing agents from fecal waste can contaminate drinking water through at least on the order of 1 - 5 meters of soil (differing depending on type of soil with its given permeability) within a few days (
source). Contamination can even spread into the 10's of meters in some instances. I think this could, at minimum, be represented by having the walls of and possibly one tile out from a cesspit being "contaminated" as in my suggestion, which would look a lot like how damp or warm stone is indicated in the game. And this wouldn't be at all taxing to performance, nor difficult for the player to see. I believe this is important, since it was a very real problem throughout history that was actively thought about and addressed when engineering sewage systems. People knew even before knowing about microbes that having poopy water too near to clean water, even separated by earth, could cause clean water to go bad. The tough part would be handling how far the contamination would diffuse into whatever body of water might be touching such contaminated tiles (if we are talking about an aquifer or large body of water).
ALSO, contaminated water should be drinkable. It should simply give a chance of infection. Having filth mixed with water making a liquid filth that is undrinkable makes this not possible. Perhaps is liquid filth could be consumed in your suggestion, but then that abstracts out the potential for quite a bit of fun things with regard to contaminating water sources, intentionally and unintentionally.
The idea is that dwarves poop into toilets, chamber pots or simply outside and in the former two cases dwarves use buckets to haul it into designated zones. It then decomposes into soil or soil nutrients which are used by farms to increase or restore fertility; actually the amount of soil represents the nutrients of the place.
That for sure is what I wanted my suggestion to plug into. Toilets/squatting pans could be connected via piping or just be placed over holes in the ground that could lead to proper sewers, cesspits, or a combination of those, or a moat, or what-have-you. Cesspits and sewers would serve the functions they did in real life: to make sure waste is collected in an area 1) away from people and their activities (to reduce health issues) and 2) for poop haulers to deal with later, or just leave to decompose. And what that stuff decomposes into are soil nutrients. Oooo... this gets me thinking of
NW_Kohaku's agriculture suggestion from way back.
Chamber pots would be an individual solution for when going outside is not an attractive option, though it would fill up after a few uses.
Going outside (or a designated area) would be the default behavior, but would of course be less sanitary, less pleasant and, just as important, less safe (did you know raptors like giant eagles can see in the UV which poop reflects, and they track prey that way?
). Also, there would be less control over where the poop ends up.