There are plenty of choices available for the hobbyist when it comes to substrate materials. When looking for an appropriate substrate you'll need to ask yourself a few questions.

1. Will I need a substrate?
2. What substrate will my fishes and/or plants require?
3. What is the appearance I'm trying to achieve?
4. Do I need the substrate to help with water conditions?
5. Will the substrate be hard to maintain?
6. What are the substrate choices?

Let's address the questions

1) For growing out fry, hospital tanks, and some breeding tanks, a bare bottom tank is sometimes preferred, as it's easier to remove any detritus, catch fish, and tear-down, than a substrate-equipped tank. Most everyone else will probably prefer to have a substrate of some sort.

2) Do some research on the fish you plan on keeping before deciding on your substrate. Many fish have specific needs, and providing them with the wrong substrate can lead to unnatural behavior, poor coloration, unsuccessful breeding, etc. A planted tank also has very special requirements, and research is also recommended. Generally speaking, there are fish that require a sand floor, and those that don't. Plants can also be generalized, some require a iron-rich substrate, and some do well without it.

3) Substrate coloration has a lot to due with the overall appearance of the tank, and to a lesser extent, the fish. A dark substrate will absorb light, giving the tank a dark, deepwater look, and will often times bring out the coloration of lighter colored fishes. A light colored substrate will reflect light, making the tank seem even brighter than it actually is, depending on the fish, it can provide a nice contrast for darker fishes, or cause a washed-out look for some of the lighter ones. It's really a personal choice, and again, do a little research and try to view tanks with different substrate "shades" before deciding what you want.

4) If you're keeping fishes that require a higher PH and hardness than your tap water provides, you may want to consider a substrate that boosts those parameters a bit. Conversely, if your tap water is adequate, or you need even lower PH and Hardness then you surely wouldn't want a substrate that takes your water even further from the desired water qualities.

5) Typically, a deep substrate will require more maintenance than a shallow one, and sand can be a bit more challenging (at first) than gravel. Try to keep your substrate at a manageable level. Unless you're using an Under Gravel Filter (UGF) I wouldn't recommend any more than 2" of gravel, with a UGF 3-4" is fine. Sand should be 2" or less, unless you want to spend extra time churning the sand to remove toxic gas pockets. With gravel, a siphon vacuum should be used every 2-3 months to remove excess detritus that's settled in the gravel. For sand, a siphon hose should be used to skim the surface of the sand every few weeks, and a good churn of the sand bed every couple months.

6) Choices in substrate are quite vast, but here's a basic rundown:

1. Sand - comes in many choices, from very affordable "play" sand all the way up to Black Tahitian Moon Sand. Sand gives the tank a very "smooth" look, and is very appealing in African Cichlid tanks. Play sand is OK, it isn't uniform in color or grain size, and contains a fair amount of clay which makes it a chore to initially clean. Silica or "blasting sand" is a good, affordable choice, it's lighter, and cleaner than play sand. The black sands are expensive, but very nice. There is a cheap black sand made from slag, and sold as black beauty, I believe it's OK for most fishes, but too sharp for sandsifting varieties. Most sands do not effect the water qualities very much, but beware of Gypsum-white sand, it will continue to raise the hardness to undesirable levels.
   
   
2. Gravel - comes in many shades, and sizes. Natural gravel is available in bulk at most fish stores, as is epoxy-coated gravel, which can range from natural to hot-pink. It's all about what you prefer. Plants seem to do best with a small grain natural gravel mixed w/ an iron source such as laterite, but that's for another topic.
   
   
3. Crushed Coral - a good choice for keeping higher PH/Hardness tanks, and will give the freshwater tank a "marine" look to it.
   
   
4. Coral Sand - same advantages as Crushed Coral, but better for fish that prefer sand.
   
   
5. Aragonite - similar to Coral Sand, but available in many shades and sizes.
   
   
6. Crushed Lime - good choice for those that want the benefits of coral without the higher price.
   
   
7. Crushed Oyster Shell - even better PH/Hardness effects than Coral, but darker and less uniform in color, also very affordable.
   
   

As you can see, there is quite a bit to consider when choosing a substrate, hopefully this helped make the decision a bit clearer for you.