I have 4 modest databases tracking business development and delivery in a professional services organisation. The DBs go through prospects, project mangagement, closure and clients. Access to the DBs is via the web feature of FP5.

What is the best way to set up the relationships between the various DBs? At the moment i use Lookup, but is it best to just keep all 4 DBs together and use direct references to the respective Dbs?

The issue that brought this on is how on the web do we update the lookup fields if they are changed, for whatever reason in a dependent database? For example, if we change the project manager through the job, how do we get dependent databases (job closure) to re-lookup automatically?

The criteria for using lookups vs the actual related field depends on what you want to happen when the related data is edited.

Example 1: You have a related file that is the price list for stuff you are selling. When you generate an invoice, you enter a stock number, and the price automatically looks up from the price list file. Months later when you change the price of the product, you don't want that change to appear in completed invoices, so a lookup is the best choice here.

Example 2: You have a client address file which contains address, phone number, etc., of your clients. When your client moves to a new address or gets a different phone number, you want this information to be immediately available in all existing parent records that reference this client. So, in this case, it's best display the actual related field from the client list.