I am writing an application that will track sales opportunities and resulting work. The work that results from sales is classified into 3 distinct types each with a different set of attributes to track thus 3 tables.
So, my question is: what is the best way to set up primary keys and foreign keys in sales opportunities for the resulting work that will be generated?
Do I need to have columns for each of the work type tables in the sales table? That would result in NULL values in 2 of the Work ID columns in the sales table for each sale.
Is there a better/cleaner way to set this up. I'm especially concerned about pulling the data back later for reporting in an efficient manner.