Database Project Setup Best Practices

Question

I'm trying to find some best practices information about setting up Database Projects, and can't seem to find anything that matches my particular situation.  I am interested in using VSTS DB for both source control and project organization.

Essentially, my development team's environment does not match the "optimal" situation that VSTS DB seems to almost require.  We manage multiple web applications, each within their own Visual Studio C# project/solution.  Unfortunately, there is no such separation by application on the database side.  All DB objects for every application are stored in the same database, which after several years of development is quite extensive. 

So, the issue I am facing is whether to create a single Database Project that includes all objects for all applications, or create up to 50 individual Database Projects, one for each web application to provide better organization.  I have performed small scale tests of both methods, and find that it's much easier to update the single project (which is important since I may be performing this task for the developmen team rather than each individual) though deployment and schema comparison become unmanageable.  Alternatively, creating individual projects leads to issues with objects that are referenced in multiple locations, increasingly long startup times, and difficulty maintaining updates. 

I had originally hoped to create individual Database Projects for each application and include those projects in the individual applications' solutions, but again due to the number of overlapping objects this did not provide a manageable solution.

I have managed to talk myself into and out of both solutions, so am looking for any advice in these or any other method that would be the best solution for my situation.

Thank you!

Answer 1

Have you thought of using partial projects?  Create separate projects for the applications you're managing to host any objects specific to them.  The overlapping objects can be stored in the partial and shared between the app projects. 

Have a look at http://blogs.msdn.com/gertd/archive/2008/11/25/visual-studio-team-system-2008-database-edition-gdr-rtm.aspx for more information.

---

Orlanzo

Answer 2

I think Probably what you want to do it Create a project that contains all your core objects, then reference that project from all your other projects so that you are not duplicating all of your elements. This creates what we call a composit project. If  you would like more information on this I would be happy to help.
Christian.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 3

Have you thought about having 1 database project and using branching to isolate development on the different applications?

You could have a release branch that matches what is in live, and branch for each application. Developers working on each application can then work on the database project in isolation from other developers working on different applications. When an application and database changes are ready to move to live, you merge from the application branch to the release branch, and then merge from the release branch to the other application branches.

We use something similar to this and it works very well for us. The only problem we have is the merges from release to application branches usually creates conflicts in the dbproj file as items will have been added in each branch. Resolving them is normally fairly simple though

Anthony