I won't embark on the difficult question of what one wants out of a category of motives, but I can make some comments on what might motivate the various choices of topologies.
Nisnevich (aka completely decomposed, aka completely decomposed étale).
If one defines cohomology with support of a closed subspace $Z \subseteq X$ in such a way to induce long exact sequences $\dots \to H^n(X) \to H^n(X{-}Z) \to H^{n-1}_Z(X) \to H^{n-1}(X) \to \dots$, then the Nisnevich topology (on the category of smooth schemes) makes sure that for any regular closed immersion of smooth $k$-varieties $Z \to X$, there is an isomorphism
$$ H_Z^\bullet(X) \cong H_{\mathbb{A}^{d-c}}^\bullet(\mathbb{A}^d) $$
when $X$ and $Z$ are of pure dimension $d$ and $d-c$ respectively.
(Notice that this statement needs smooth schemes to work).
One example of a very serious consequence of this is the Gersten exact sequence for homotopy invariant sheaves with transfers [Voevodsky, Coh.th.of.presh.w.transfers, Theorem 4.37]. Others include Localisation [Morel-Voevodsky, $\mathbb{A}^1$-hom.th.of.sch., Theorem 3.2.21], and Purity [Morel-Voevodsky, $\mathbb{A}^1$-hom.th.of.sch., Theorem 3.2.23].
For other benefits of the Nisnevich topology (cohomological dimension $=$ Krull dimension, $K$-theory has descent, direct image is exact) see [Morel-Voevodsky, $\mathbb{A}^1$-hom.th.of.sch., Beginning of Section 3].
Finite (a.k.a.fs, a.k.a. finite surjective).
This is the topology on the category $Sch/k$ of separated finite type schemes whose covering families are jointly surjective families of finite morphisms. It is a way to build in transfers, in the sense that the canonical functors
$$Shv_{fs}(Cor/k, \mathbb{Z}) \stackrel{\sim}{\to} Shv_{fs}(Sch / k, \mathbb{Z})$$
$$PreShv(Cor/k, \mathbb{Q}) \stackrel{\sim}{\leftarrow} Shv_{fs}(Sch / k, \mathbb{Q})$$
are equivalences of categories. (The situation is more subtle if one wants to use only smooth schemes).
One important use of this is to compare "cycle theoretic" theories (like Suslin homology, or Bloch's higher Chow groups) with "sheaf theoretic" theories (like étale cohomology). Cf. [Suslin-Voevodsky, Sin.hom.of.ab.alg.var.] where they prove that algebraic singular homology $H^{sing}_i$ (now called Suslin homology $H^S_i$) can calculate the classical topological singular homology (which can be calculated via sheaf cohomology) for $\mathbb{C}$-varieties $X$.
$$ H_i^{S}(X, \mathbb{Z} / n) \cong H_i(X(\mathbb{C}), \mathbb{Z} / n) $$
Cf. also Suslin's article "Higher Chow groups and étale cohomology" where he shows that Bloch's higher Chow groups calculate étale cohomology with compact support for equidimensional quasi-projective varieties over algebraically closed fields $k$ with char.$k \nmid m$, $i \geq d = $dim $X$.
$$CH^i(X, n; \mathbb{Z} / m) \cong H_c^{2(d-i) + n}(X, \mathbb{Z}/m(d-i))^\# $$
The qfh-topology.
This is the topology generated by the Zariski topology, and the finite topology.
$$ \mathrm{qfh} = \langle \textrm{finite surjective, Zariski} \rangle. $$
Remark: It is finer than the étale topology, so we have qfh $= \langle $fs, Zar$ \rangle = \langle $fs, Nis$ \rangle = \langle $fs, étale$ \rangle$.
Proper cdh (which should be called the cdp $=$ completely decomposed proper topology).
Every scheme admits a Zariski covering by affine schemes. So properties of Zariski sheaves are completely determined by their values on affine schemes. Analogously, (if the base field admits resolution of singularities) every (finite type) $k$-scheme admits a proper cdh covering by smooth $k$-schemes. So just as we have
$$ Shv_{Zar}(Aff/k) = Shv_{Zar}(Sch /k) $$
we have (if res.of.sing. holds for $k$)
$$ Shv_{cdp}(Sm/k) = Shv_{cdp}(Sch /k). $$
(the sites on the left are equipped with the induced topologies).
Cohomologically, (and in general, i.e., without any dependance on res.of.sing) for any proper birational morphism (i.e., blowup) $X' \to X$ which is an isomorphism outside of a closed subscheme $Z \subset X$, setting $Z' = Z \times_X X'$ there is canonical long exact sequence
$$ \dots \to H^n_{cdp}(X, F) \to H^n_{cdp}(X', F) \oplus H^n_{cdp}(Z, F) \to H^n_{cdp}(Z', F) \to H^{n + 1}_{cdp}(X, F) \to \dots $$
for any cdp sheaf $F$, and the same is true of any topology finer than the cdp topology (e.g., cdh, h, see below). In fact, the cdp topology is the coarsest topology with this property. In fact, if a strong version of resolution of singularities holds, the cdp topology is the coarsest topology with the above long exact sequence for blowups of smooth schemes in smooth centres.
cdh (a.k.a., completely decomposed h-topology).
The cdh topology is generated by the proper cdh topology and the Nisnevich topology.
$$ \textrm{cdh} = \langle \textrm{Nisnevich}, \textrm{proper cdh} \rangle. $$
So we get the above cohomology with supports property for smooth closed immersions, the blowup long exact sequence, and, assuming res.of.sing. the power to reduce arguments to smooth schemes.
Inconsequential remark: The name is a slight misnomer. We have $h = \langle$ étale, proper $\rangle$, and $cdh = \langle$ comp.dec.étale, comp.dec.proper $\rangle$, but while every cdh covering is a completely decomposed $h$-covering, the converse is not true, comp.dec.h $\neq$ cdh. There are completely decomposed flat coverings which are not cdh coverings.
Proper.
The topology on $Sch/k$ whose coverings are jointly surjective families of proper morphisms. Without assuming resolution of singularities, a theorem of de Jong on alterations implies that every $k$-scheme admits a proper covering by smooth schemes.
$$ Shv_{\textrm{proper}}(Sm/k) = Shv_{\textrm{proper}}(Sch /k).$$
The h-topology.
The $h$-topology is generated by the proper topology and the Zariski topology.
$$h = \langle \textrm{proper}, \textrm{Zariski} \rangle.$$
It is generated by a number of other combinations of the previously mentioned topologies, e.g., $h = \langle$cdh, finite$\rangle = \langle $proper, étale $\rangle$.
$L$dh (a.k.a. $L$-decomposed topology)
The proper topology has the disadvantage from the cdh topology that many interesting cohomology theories satisfying cdp descent, only satisfy proper descent after passing to rational coefficients. Between cdp and proper lies the $L$dp-topology. If $L$ is a collection of primes, then one defines a morphism $f: Y \to X$ to be $L$-decomposed if for every (not necessarily closed) point $x \in X$, there exists a point $y \in f^{-1}\{x\}$ such that $k(y) / k(x)$ is a finite field extension of degree prime to every element of $L$. If $L = \{\ell\}$ then we just write $\ell$dp. We have $\varnothing$dp $=$ proper, and $\mathbb{P}dp = cdp$ if $\mathbb{P}$ is the set of all primes.
It follows from a theorem of Gabber that every for any choice of $\ell \neq $char.$k$, every (finite type) $k$-scheme admits an $\ell$dp-covering by smooth $k$-schemes. Many cohomology theories of interest satisfy $\ell$dp descent after passing to $\mathbb{Z}_{(\ell)}$-coefficients, so we can still obtain information about their $\ell$-torsion using this topology.
Just as we have $cdh = \langle $Nis, cdp$\rangle$ and h $= \langle$ Nis, proper $\rangle$, we could define
$$ \ell dh = \langle \textrm{Nis}, \ell dp \rangle. $$
However, in practice, it is much more useful to use the definition $\ell$dh $ = \langle$ cdh, fps$\ell' \rangle$ where fps$\ell'$ is the topology whose coverings are $\ell$-decomposed families of finite flat surjective morphisms, see below. This way questions about $\ell$dh descent be be broken up into a cdh descent part and an fps$\ell'$ descent part.
Here is the relationship:
$$ \begin{array}{cccc}
\textrm{Set of primes} & \textrm{Topology} & \textrm{Coefficients} & \textrm{Theorem} \\ \hline
\textrm{all primes} & cdh & \mathbb{Z} & \textrm{Hironika's res.of.sing.} \\
\{\ell\} & \ell dh & \mathbb{Z}_{(\ell)} & \textrm{ Gabber's theorem on alterations } \\
\varnothing & h & \mathbb{Q} & \textrm{ de Jong's theorem on alterations } &
\end{array} $$
The fps$\ell'$ topology.
This is a version of the finite topology. It is generated by morphisms which are finite flat surjective of degree prime to $\ell$, where $\ell$ is a prechosen prime number. The point is that it can be much easier to show that a cohomology theory has "trace" morphisms for finite flat morphisms, than that it has transfers. If one defines an appropriate notion of presheaves with traces (cf. https://arxiv.org/abs/1305.5349) then it is easy to show that every presheaf of $\mathbb{Z}_{(\ell)}$-modules with traces is an fps$\ell'$ sheaf, and one can leverage this to show an equivalence of categories
$$ Shv_{cdh}(Cor/k, \mathbb{Z}_{(\ell)}) = Shv_{\ell dh}(Cor/k, \mathbb{Z}_{(\ell)}) $$
Note, the same is true with h and $\mathbb{Q}$ replacing $\ell$dh and $\mathbb{Z}_{(\ell)}$.
