IDC layed it out pretty well but let me clarify just a bit.
Your whole system speed is based off the FSB.
Intel CPUs are quad-pumped so their interface is four times the FSB (200x4=800, 266x4=1066, 333x4=1333, 400x4=1600).
DDR/DDR2/DDR3 clock speed is double the FSB. So DDR2-667 is the speed you need to run an FSB of 333 with a 1:1 ratio (DDR2-800 for FSB 400, DDR2-1000 for FSB 500, etc).
DDR3 is extremely fast (DDR3-1600 for example can handle FSB 800) but there are no motherboards or CPUs out that require these kind of speeds even for overclocking. To take advantage of DDR3 you would have to run a higher memory ratio (1:2 so it runs at twice the speed, example: FSB 400 then 1:1 is 800MHz but 1:2 is 1600MHz).
Typically higher memory speed doesn't net much (if any) real-world advantage and is only helpful in benchmarks and certain very selected applications (F@H).
Short answer: get good-quality DDR2-800 and you're good to go. This will handle stock speeds without problem and should overclock to DDR2-1000 speeds if you decide to get some additional performance out of your CPU.