On the NVIDIA side, the drivers are the official NVIDIA binary driver, the 2D "nv" driver, and the reverse-engineered Nouveau driver. The closed-source NVIDIA driver supports both their GeForce and Quadro product series, however, the support is even broken down into different binary drivers. For hardware older than the GeForce 6 series, there is a (closed-source) NVIDIA legacy driver and for the oldest of hardware is another legacy driver. Support for older NVIDIA hardware is discontinued from their mainline Linux driver; however, with major kernel and X.Org releases they will generally back-port these support changes to the legacy drivers. This official support is also bound to 32-bit and 64-bit Linux.
This official binary driver can be downloaded from NVIDIA's website but can also be found in a re-packaged form in some distribution package repositories. For instance, Fedora users can find this driver at Livna, but that will soon become known as RPM Fusion once it has merged with Dribble and FreshRPMs (two other third-party Fedora/Red Hat repositories). Ubuntu users can find the NVIDIA driver (including the legacy drivers) in the Ubuntu restricted repository and can be installed through Ubuntu's Restricted Driver Manager. One of the problems, however, is that these package repositories may not always be in harmony with the latest driver version from NVIDIA. From time to time, NVIDIA will also publish a new driver but in beta form.
On the NVIDIA side but in open-source form, there are the nv and Nouveau drivers. The "NV" driver is officially maintained by NVIDIA, but it's bound to 2D support without any X-Video support and code-wise it's not very pretty. While this 2D driver is lacking, it is updated to support new NVIDIA hardware and is open-source so it ships with most all desktop Linux distributions.
For an open-source 3D NVIDIA driver there is Nouveau. The Nouveau driver is clean room reverse engineered from the official NVIDIA driver. This driver is unified to support all NVIDIA GPUs from the earliest TNT devices to the latest in the GeForce 8 series. However, the level of support for different NVIDIA chipsets at this time varies. The 2D component of Nouveau is essentially stable with RandR 1.2 support and X-Video. However, the 3D portion of Nouveau is still very much a work in progress.
No Linux distribution ships with the Nouveau driver by default right now, however, RPM packages are available in the Fedora repository. There are distribution-specific packages of Nouveau available for other distributions; however, with the rapid rate of commits to the xf86-video-nouveau driver and DRM, they are generally out of date. For experienced Linux users, the Nouveau driver can be checked out of its FreeDesktop.org git tree. 2008 should be a very interesting time for open-source support on the NVIDIA side.
If you are after 3D support, right now the binary NVIDIA driver is your best option alas its closed-source. Though once the Nouveau driver is stable for 3D and starts beginning to appear in Linux distributions by default, it will be a different story. The open-source Nouveau driver will also work on alternative architectures such as PowerPC or even with the PlayStation 3 cell processor.