Expansion and contraction will be your biggest enemy,
assuming you do the whole installation properly.
Any weak link in the installation will cause a failure.

Using cementboard as a backer will help the
expansion/contraction problem, but not eliminate
it. If you insist on tiling, at least use porcelain
tiles which are more dense, absorb less moisture
and are less prone to expand/contract.

There are also "flexible" thinsets available, and
when grouting it would help to not grout around
the perimeter, but use a flexible sealant to allow
the teeny bit of flexing. I've seen dozens of
buckled tile floors that lifted right up when they
expanded just 1/8" and had no where to go.

I'd have doubts about the area you said gets
flooded from time to time. The wood there may
be warped or loose.

You have a situation that requires every step to
be done correctly. It has all the elements that
cause failures: temperature extremes, over
wood framing (not a slab), old wood subfloor,
lot's of traffic/abuse potential, and you're not
an expert installer.

I'd say to get yourself some new indoor/outdoor
carpet, and when the kids grow up and move
out, do the tilework......

thetiler