It depends on the project.
To be short:
SDF: it is the database format of
SQL Server Compact edition[
^]. This is the embedded edition of sql server. Think about it as a "single-dll" sql server. Thus it will run together with the application that is using it. It is not a service, it is not a real server. But if your database is accessed from a single application (even a web application that is multi-user), this is a good approach, although it has some limitations.
MDF: this is the database format of the "big", real server editions. Indifferent which one you use (from express to datacenter), this is the format. But it depend on many things, which edition you need/can use - one of this things is the amount of money you are willing to pay :). Please see this really short and comprehensive edition comparsion chart:
http://www.sqlserverspace.com/sqlservereditioncomparison.html[
^], and this if you need more details:
http://msdn.microsoft.com/en-us/library/cc645993.aspx[
^]