Any more than two is overkill. Back tees and forward tees, and have the tees at the front of the back tees for regular play.
While I agree that 6 sets of tees is unnecessary, calling more than two tees "overkill" is ridiculous. Chris, are you a low handicapper that doesn't care about the enjoyment of the game for people who cannot hit the ball a long distance? Remember that the average golfer cannot carry the ball over 200 yards.
Take a typical upscale daily fee type of course, where the tips play between 6700 and 7000 yards. Where are your forward tees? If they are longer than 5700 yards, many women and seniors will be miserable. So where do I, as a mid-handicapper, play this course? I cannot enjoy a round from 7000 yards, and 5700 yards will probably not challenge me (plus my friends would ridicule me for playing from the same tees as grandma!).
I think 3 or 4 sets of tees is perfect -- 4 for a more difficult course, especially one with longer forced carries from the tees.
I guess it comes down to what is your goal when designing a course: if it is to challenge and please all your golfers, 2 tees will fail to do this at all but the easiest of venues.