Can David become one of the greatest villains of all time

Is it possible he blows us all away in A:C and whatever movie after that and is even in the conversation for "Greatest"? Sure

Is it likely? Not really.

Vader is the goat for sure, a legendary icon (and my personal favorite along with The Joker) but he's more of a classic straightforward villain compared to the more multifaceted David who is rather hard to define imo.

We still don't have the full picture yet but this is based on my observations from Prometheus and also analysis of AC trailers and clips:

Is he "good"? So far, not really imo (depends on who he likes/dislike it seems) but the last nail in the coffin that effectively cleared away all doubts for me was him infecting Holloway, possibly due to an act of revenge (at least partly considering him obviously reacting emotionally or offended by insults) or just a cold and calculated plan too see the effects of the goo. Curious child.

Either way, I certainly don't consider that act "good" in any way regardless of how much I disliked Holloway. However, he has done some good things which just makes him even more interesting although confusing.

Is he "bad"? In our general human viewpoint of moral and ethics - yes, again he spiked Holloway's drink. Not to mention that he (based on an observation from the latest screening....  

[Spoilers below]

....lured Oram towards an egg as the "final ingredient" 

[Spoiler end]

Is he a curious psychopathic mastermind? Oh yes, and I guess that's the best way I could sum him up.

I definitely think David has the potential to be a memorable villain, not only because the character is interesting, but much thanks to Fassbender's incredible performance.