As always, there's a tradeoff. Let's say you're going for the same 1:1 reproduction ratio with both lenses (i.e., taking a picture of something the same size as your sensor). A 50mm macro will need to be significantly closer than a 100mm macro. Advantage goes to the 100mm. On the flip side, the 100mm will have less depth of field at the same aperture. Advantage goes to the 50mm. The longer lens will also tend to compress the differences in depth, giving you a "flatter" image, while the shorter lens will tend more to exaggerate things. Advantage (although it's really an artistic question) probably to the longer lens.

At the limit, many point-and-shoots have incredible macro capabilities. The lens is practically touching its subject when you're shooting at the closest. You can take hand-held macro shots of flowers without flash and with exceptional depth of field due the incredibly short focal length of the lens. The tradeoff is that you don't always want to get so close. How close exactly are you comfortable getting to that bee on a flower? Also, the lens casts its own shadow. (Although there's at least one Pentax point-and-shoot with LEDs in a ring around the lens to help with macro illumination.)

And, yes, there's really not much difference between a 50mm "standard" lens versus a 50mm "macro" lens, except that the macro lens is designed to rack itself further away from the camera body. That's what macro extension tubes are all about. Generally speaking, what really differentiates macro lenses is that they have exceptionally high quality optics but not necessarily the widest aperture.