I'm creating a new database for our company. We have elected to begin with the FM Starting Point. It has most of the elements we need, however, one very important aspect of our business needs to be added.
We are a vendor to the property management industry. Therefore, we have property management accounts and property accounts. Both are account records. We need the ability to relate the property account to the management account. A simple parent child self join should work for this. Add a portal and we can see all the property accounts that the management company has. However, we also need the ability to carry this relationship through the rest of the tables.
We would like to see all the estimates, projects, invoices, etc of the child accounts (properties) on the parent account (management) portals. As well, we need the ability to list management account contacts to property accounts and carry that relationship through to the other tables as well.
What is the best structure and methods to hook everything up that keeps excellent performance? Is a parent child/self join the best method? I really appreciate your help.