Personally, I LOVE my Australian Cattle Dog. He is super intelligent, loyal and a fantastic watch dog. He's our alert system for when the sheep start lambing, if an animal is out or if something isn't right. They do have to be thoroughly socialized as a puppy. I got mine when he was a bit older and we're still working through some difficulties. I would thoroughly research any breed before getting a new puppy. The Cattle dogs are very mouthy and may not work well with a bunch of little kids.
The Australian Shepherds are also great dogs. We've had two mixes and they were both very sensitive, super intelligent dogs. Great with kids and people. Neither of them were big fans of large animals, preferring sheep and people to horses and cows. But that might have just been them.
Any of the herding breeds require lots of time and training. They can be destructive if bored and work best if given a job to do. And of course... they will be most destructive on the most expensive piece of tack in the barn.... every time.