Linux is a great platform for video, DVDs, and other multimedia content because it is a popular task and the code is opensource. So as long as there is a nerd out there who wants to be able to watch some obscure video format on his computer there will be free software for Linux to play it.
A quick note: Some audio and video formats require MS Windows codecs to be installed to work with Linux. A google search for "win32 codecs" turns up some good information on how to install these for your distribution. If you're using SUSE, you can download the w32 codec RPM here. Further, it is illegal in some countries to use some software to watch DVDs on Linux. This is a complicated subject and I'll leave it to the reader to research what is legal in their area and decide for themselves if they want to comply with a law that prohibits them from watching a DVD they bought on their computer.
Another note: Some Linux distributions intentionally cripple some of their media playing capabilities due to what kind of free software can be shipped with other kinds of software and patent concerns. SUSE is such a distribution, so to get many of the most popular media formats to play with SUSE, you'll need to remove some versions of media software shipped with SUSE and install non-SUSE versions. This is not very difficult, a decent summary on how to get this going can be found here.
Xine is a popular media player for Linux. It supports countless audio and video formats including DVD. Kaffeine is KDE's front-end for xine; this basically means that what you see is Kaffeine, but Kaffeine is running on top of Xine. Xine is great, but if you don't like Kaffeine, there are plenty of other GUI's you can use. Totem is GNOME's front end for Xine; you can see several others here.
MPlayer is pretty much tied with Xine as the Linux video player of choice. It plays bucketloads of media formats; you might just need to try them both to decide which you prefer. I have found MPlayer to work better at playing DVDs on older hardware, but use Xine on machines with newer hardware. Like Xine, you'll need to pick a front-end to use with MPlayer if you want buttons and mouse control. MPlayer has more skins than an 18th century fur trader, you can take your pick here. I will also say that I love MPlayer's plugin for Firefox; you install it and it plays imbedded media in Firefox just like Media Player does in MS Windows. This is a seperate download and is usually made available in native package formats by each distribution (there is an RPM available for SUSE).
Noatun is another media player for KDE. I've never used it and know next-to-nothing about it. I've included it here for the sake of variety.