2 Replies Latest reply on Apr 27, 2015 8:06 AM by Mike_Mitchell

    Best Practices for Multi-Department Databases


      Hello all,


      I have a project coming up that is now an addition to another project that was completed (if one can ever say completed).


      The first project was for one department of a City Government, done working great. The second is for another like department that could use parts of the original departments info (ie customers). When creating the first project the second was not consider in any way as part of the scope of the overall project.


      How should I create the second database? I have gone over back and forth in my mind best ways to tackle it. Should I just add to the original db and grow its ability? Should I pull out like tables and create a common db that each looks to for like data? Should I build a second for the new of use external connections for the like tables?


      Any thoughts are appreciated



        • 1. Re: Best Practices for Multi-Department Databases

          If you billed it as an addition, then don't make extra work for yourself by rebuilding it from the ground up.


          Sounds to me like you should clone the file and start from that point to meet your needs for the new department.


          Hopefully you've clearly explained the limits of the budget you outlined with your client.

          • 2. Re: Best Practices for Multi-Department Databases

            A decision like this is usually best made based on use case. Ask yourself several questions:


            1) Do these departments share just the structure of the common tables, or do they share the actual data?

            2) Do these departments share a common or very similar workflow?

            3) How much drift in workflow and / or data and / or feature set can reasonably be anticipated between the two departments?

            4) Is it reasonable to maintain a common set of code between the two customers, or are their demands sufficiently different you'd be better off maintaining it as two separate systems?


            The answers to these and similar questions will usually guide you. For example, if they share common table structure, but not data, then you're better off cloning what you have and running it separately. OTOH, if they have a common set of data, then you might be better off going with a separated model, giving each department its own interface file and tapping a common back-end data file.


            If the workflows are very similar, then perhaps a common interface file works well. OTOH, if the workflows are only superficially similar or expected to drift, then you're probably better off keeping them separate.


            Remember that, if you go with separate databases, you'll be maintaining two systems instead of one. That's a consideration for things like your time - and the customer's budget, as Mike B. alluded to.