And how does it work if I have an iPhone and my wife has an iPhone and we share a family iPad and iMac? Do all apps, photos, and music get automatically synced to all devices?
You would have to sync all devices with one iCloud ID to have the exact same apps, music, settings, etc sync to all devices the same. Your wife and yourself should both create separate iCloud ID's, you will have 10GB between the two of you. You may just need to pick whose iCloud account will run on the iPad and Mac.
As far as iCloud being something you're frustrated with at this point, I'd wait until it launches to say whether you hate it or not. It's still being tested and the beta technically hasn't ended yet. If you sync to iCloud now, the day it launches Apple will likely wipe all iCloud storage and start fresh, again.
It's a very neat feature. The minute you download a new app, it syncs to your iPad, iPhone, and iPod touch. As far as syncing music, you have to purchase the music from iTunes for it to sync to all devices, or you have to be an iTunes Match subscriber to have all of your music. Either way, this feature will grow on everybody. It's difficult to understand completely at first, but you will.
Also, in the long run, almost everyone with an iOS device will end up using iCloud. It's great to not have to constantly have your charging/syncing cable free just to sync new apps and music, also wireless syncing is a wonderful thing. I assume at some point, possibly with the iPhone 5, Apple may use wireless charging methods as well.