I personnally use MS Access DBs (in fact the native DB format recognized by VB, called MS DAO Jet Engine in the docs)
MS Jet provides all you need about full relationnal structure, including one-to-one, one-to-many, and many-to-many relations, dependency (i.e. cascading updating and deleting)
BUT, (yes, there are drawbacks), Access is limited with huge DBs (I use 200 MB DBs, and it's SLOW).
I don't know the strength of VFP, but if you have to manage big DBs, and have integrity rules and keep your data coherent, I think you could consider SQL Server, rather than MS Jet.
Microsoft provides a white paper onto its web site. It discusses tech choices and analyses differences between Foxpro, Access, and SQL Server. That's not really impartial, but it gives a starting point. Perhaps you could study it, and compare it to similar docs from Oracle, Informix, Ingres, and so on.