Best Practice in Linking tables through an application

[Benades]

Hi,

I have an application that I am building for an engineering company that would consist of the main departments, namely: Sales, Manufacturing, Installations, and Finance. A key function of this application is traceability between departments.

What would be the best practice in linking these department documentation together?

 

Scenario 1:

By creating a centralized table that contains the quote #, manufacturing #, installation #, invoice #, and status. Each department table updates the centralized table to keep track throughout the process.

Scenario 2:

Having one single table for all the mentioned departments. I foresee some problems with this solution.

Scenario 3:

Each department has its table referring to the previous department inside their department table and deploying by using views to combine the necessary data.

 

Do you have any suggestions or comments on the different scenarios? Perhaps even guides pertaining directly to the problem at hand.

[kpcollier]

Personally, I would only have one instance of Sales, Manufacturing, Installations, and Finance each. I would add a DepartmentID column to each of them. Then create a Departments Table with a unique ID field. 

You'll use the ID of the department in Sales, Manufacturing, Installations, and Finance to be able to tell which records belong to who. When creating dashboards or something to access the tables, you'll pass the user's Department to filter the results to only their department. 

Having many instances of the same table gets really messy very quickly, specially when you need to make changes or you are having problems. All of a sudden, 1 problem to fix becomes 5+ problems to fix. Also, comparing values/records from all departments would be a lot harder when you need to join multiple instances of the same tables instead of just one table to show all data. 

This is the normal database design. 

 

[Tubby]

For me, I will actually do something similar to @kpcollier's setup. Just maintain a lookup table for the departments so you only store IDs on your main table. I agree with creating just one instance of the record. I imagine the records will go through a process where multiple departments will update or approve the data. In this case, what I will do is create a field to tag what department should the record be checked/sent. You can create a triggered action or a DataPage workflow so the record can be assigned to the next department after updates.

[NiceDuck]

I agree with the issue you are seeing in Scenario 2.

While it is plausible to have a single table for all, it will be a mess once everyone is accessing that table at the same time. Assuming that you will be having about millions of records on that table and everyone is making changes here and there. 

 

[NiceDuck]

My idea on this one is a combination of scenario 2 and 3.

A single table will act like a core and has the main details for the records. There will also be a column that will indicate when was the last update, from where the last update come from, which or who made the latest pull of the record and status.

Then, each department will have its own table which is a similar setup to the core table. If they have to do something on a record on the core table, they will have to pull it first on their department table. They will then process the record on their table and only transfer it back ( by updating the core table, preferably via triggers) to the core table once they are done.

No one should directly make changes to the core table.

With this, we can keep a healthy record on the core as they have to finalize the changes on their end first before throwing it back to the core. Also, we can keep track on which department is currently working or last worked on the records.



 

[NiceDuck]

One last thing, unless you are sure of what you are doing, avoid enabling referential integrity in the table relationships. Especially if you have triggers. In most cases, that setting will prevent your triggers from working as you intended.