Title
One DB related to multiple DBs
Post
I have a quotation DB that allows everyone in the company to generate quotes. Our company has 4 different departments that each have thier own DB for keeping track of jobs once they come in. I am trying to come up with an efficient/elegant way to relate the quotes to each of the tracking DBs.
What I am looking to do is - be able to show information from the tracking DBs in the quote file so that we can see which quotes turn into a job (or jobs), start/completion dates, etc.
I currently have relationships set up for each of the 4 tracking DBs to the QuoteDB based on quoteID. The four separate layouts for each dept that have a portal based on that dept's relationship to show records from the tracking DB. Is there a way to do it with a single portal that looks at all four tracking DBs and finds the related records?
You have two options and different experienced developers will recommend differently here.
Option 1:
Pull all the fields from all the different files into one table. For any given record, only the fields needed for that department would be used. This often requires less restructuring of your system to make it work as each seperate table can be replaced with a reference to the same common table.
Option 2:
Do as you describe and then link a "department specific" table in a one to one relationship to the new table. This avoids having all those "unused" fields and ultimately can look and function just like using the same table as in Option 1. It does, however, require adding a new Table Occurrence box to each file's Relationship graph as you now have two related tables taking the place of the original. It can, however, reduce the amount of updating you need to do on your layouts if you retain the current tables as your "department specific" tables.
Take your pick, the end result is nearly identical from a user's persective.