AnsweredAssumed Answered

One DB related to multiple DBs

Question asked by mgores on Mar 4, 2011
Latest reply on Mar 4, 2011 by mgores

Like • Show 0 Likes0
Comment • 8

Title

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?

philmodjunk Mar 4, 2011 11:52 AM

Correct Answer

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.

See the reply in context

No one else had this question

Outcomes

philmodjunk Mar 4, 2011 9:02 AM

Is there a way to do it with a single portal that looks at all four tracking DBs and finds the related records?

Yes, but it requires pulling on the Job records from your 4 different departments into a single table. It is indeed possible for your 4 departments to access the same jobs table with portal filters, finds and security settings set up to limit each department's access to only the Job records for their department even though they are all accessing data from the same table.

Actions

mgores Mar 4, 2011 9:21 AM

The problem I have there is that the 4 department trackers are separate files and have been pretty much been set up separately. Though they all have the fields I want to show in the portal, they are not necessarily named the same, and they have many other fields that are dept specific. As far as security I am only looking to view the data from the trackers in this portal, so would not allow editing or record create or delete through the portal, and the idea is to allow anyone with access to the Quotes DB to see the related data in the trackers.

Are you suggesting a separate table in the Quotes DB for the tracker data, then importing data from each of the four dept trackers?

Actions

philmodjunk Mar 4, 2011 10:00 AM

Best solution would be to merge all the data into a single table and then modify those 4 separate files to use the same merged table as an external data source.

You could import from the separate tables, but if you are able to do that, then taking that merged table and using it with each of the 4 separate files also makes sense and eliminates the issues of having to constantly import the data in order to keep the merge table up to date.

My reference to security settings is just to list it as one of the main tools for helping restrict users to a given subset of the total records in the database--in this case restricting a user's access to just the Job records for their department.

Actions

mgores Mar 4, 2011 11:19 AM

So would I create a new table in the Quotes DB that has the common fields (QuoteID, Job#, cust name, etc), then change each of the individual tracking files to use that new table as the source for the common fields? i.e. for each department tracker change those fields to be

MasterTracker::Fieldname instead of DeptA:Fieldname?

Then the other fields that are unique to each Department's file would still be DeptA::Fieldname?

Actions

philmodjunk Mar 4, 2011 11:52 AM

You have two options and different experienced developers will recommend differently here.

Option 1:

Option 2:

Take your pick, the end result is nearly identical from a user's persective.

Actions

mgores Mar 4, 2011 1:00 PM

Let me know if I am misreading here.

If I have the Quotes DB and four dept tracking DBs, call them DeptA, DeptB, etc

I set up a table in Quotes called MasterTrack, populate that with the fields I want to show (job#, dates in and out, customer name, part number, etc)

Then in each of the four tracking DBs set up a relationship to the MasterTrack table. For each instance of the common fields on the layouts, say Job#, I change it from DeptA::Job# to MasterTrack::Job#.

That way as a job is entered into the DeptA database those fields actually get entered into the MasterTrack table. The individual dept DBs just show those fields from the MasterTrack table, but keep their other unique fields.

Actions

philmodjunk Mar 4, 2011 1:03 PM

You got it.

Actions

mgores Mar 4, 2011 1:11 PM

Hey Phil, thanks for all your help with these issues. Am coming up with a pretty tight system thanks to your help.

Actions

8 Replies

philmodjunk Mar 4, 2011 9:02 AM
Mark Correct
Correct Answer
Is there a way to do it with a single portal that looks at all four tracking DBs and finds the related records?

Yes, but it requires pulling on the Job records from your 4 different departments into a single table. It is indeed possible for your 4 departments to access the same jobs table with portal filters, finds and security settings set up to limit each department's access to only the Job records for their department even though they are all accessing data from the same table.
Like • Show 0 Likes0
Actions
mgores Mar 4, 2011 9:21 AM
Mark Correct
Correct Answer
The problem I have there is that the 4 department trackers are separate files and have been pretty much been set up separately. Though they all have the fields I want to show in the portal, they are not necessarily named the same, and they have many other fields that are dept specific. As far as security I am only looking to view the data from the trackers in this portal, so would not allow editing or record create or delete through the portal, and the idea is to allow anyone with access to the Quotes DB to see the related data in the trackers.

Are you suggesting a separate table in the Quotes DB for the tracker data, then importing data from each of the four dept trackers?
Like • Show 0 Likes0
Actions
philmodjunk Mar 4, 2011 10:00 AM
Mark Correct
Correct Answer
Best solution would be to merge all the data into a single table and then modify those 4 separate files to use the same merged table as an external data source.

You could import from the separate tables, but if you are able to do that, then taking that merged table and using it with each of the 4 separate files also makes sense and eliminates the issues of having to constantly import the data in order to keep the merge table up to date.

My reference to security settings is just to list it as one of the main tools for helping restrict users to a given subset of the total records in the database--in this case restricting a user's access to just the Job records for their department.
Like • Show 0 Likes0
Actions
mgores Mar 4, 2011 11:19 AM
Mark Correct
Correct Answer
So would I create a new table in the Quotes DB that has the common fields (QuoteID, Job#, cust name, etc), then change each of the individual tracking files to use that new table as the source for the common fields? i.e. for each department tracker change those fields to be

MasterTracker::Fieldname instead of DeptA:Fieldname?

Then the other fields that are unique to each Department's file would still be DeptA::Fieldname?
Like • Show 0 Likes0
Actions
philmodjunk Mar 4, 2011 11:52 AM
Unmark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
mgores Mar 4, 2011 1:00 PM
Mark Correct
Correct Answer
Let me know if I am misreading here.

If I have the Quotes DB and four dept tracking DBs, call them DeptA, DeptB, etc

I set up a table in Quotes called MasterTrack, populate that with the fields I want to show (job#, dates in and out, customer name, part number, etc)

Then in each of the four tracking DBs set up a relationship to the MasterTrack table. For each instance of the common fields on the layouts, say Job#, I change it from DeptA::Job# to MasterTrack::Job#.

That way as a job is entered into the DeptA database those fields actually get entered into the MasterTrack table. The individual dept DBs just show those fields from the MasterTrack table, but keep their other unique fields.
Like • Show 0 Likes0
Actions
philmodjunk Mar 4, 2011 1:03 PM
Mark Correct
Correct Answer
You got it.
Like • Show 0 Likes0
Actions
mgores Mar 4, 2011 1:11 PM
Mark Correct
Correct Answer
Hey Phil, thanks for all your help with these issues. Am coming up with a pretty tight system thanks to your help.
Like • Show 0 Likes0
Actions

One DB related to multiple DBs

Title

Post

Outcomes

Related Content

Recommended Content