Where to put my database project in TFS?

Answer 1

In this case I'd put database project into separate project and just reference it from other projects.

IMHO if your database (either single instance of database or just common database schema) is shared between multiple project, then you need to treat your database schema (or at least large parts of it) as an interface exposed to those project. This interface/API needs to be change controlled and versioned independently to any single project. So database becomes an external dependency to all of those projects, just as any shared library or server.

Now. You are using TFS. There comes a cost of having separate projects in TFS. TFS has its branching model based on single namespace with project tree. That requires a lot of care and discipline or you would mess up merges etc.

Add to that complete lack of support for dependencies between projects. If you want to change-control dependencies between projects you have to either branch a project as a subproject of dependent project (with all branching and merging tracking problems I mentioned above) or teach your build system to get the right versions from TFS all the time (which I find scary).

Notice that project 'Everything' will not help if each of its parts needs to be released/versioned separately. You mentioned 'feature branches' for subprojects, etc. It is going to be an even worse mess than separate projects which, at the very least, are explicit. If you all openly agree to maintain common database project, there will be no surprise later when you discover you need to negotiate each change to it among all your other project teams.

So, in the end, it's your decision. Are all the projects involved separate enough to version them as separate projects or not. Are they going to diverge or not. Do you want or need them to diverge at all? Is it worth the cost of additional work involved?

---

And on a completely different level. All you are talking about is partitioning your code base vertically into layers--here comes database project (with database schema etc), here data access, here business logic, etc....

Have you considered partitioning horizontally into domain level blocks, each block complete and backed up by its database schema. Your project would depend not just on common database schema but on one or more shared modules. And each of them would bring its own part of database scripts.

I'm just thinking here. I'm not really sure if that would be better or if it would even work out in practice. Anyone can share his or her opinion?

Answer 2

Thanks for the responses, really great.

The idea of treating the database as an API is interesting, it is really like that. The database is populated from numerous sources, some internal, some external, some apps get data out that was input via another app. Controlling it as an API that each app dips in and out of is a very helpful analogy, thanks for that.

The cost of having no support for dependencies is a concern, but I think we can be disciplined enough to do our branching in an orderly manner. The branching of the app codebase is likely to be more involved than the database - we really just want the database under source control in some way to satisfy Sarbannes-Oxley audit requirements.

I will have to think some more on this.

The horizontal partitioning of the db isn't something I'd considered. It feels like too much of the database objects are shared to be able to chop it into horizontal blocks - we'd have the same objects (tables/sprocs etc) in several blocks which I think would confuse us further.

Answer 3

The comment about treating the database like an API is right on. While the implementation is very different -- building & deploying a database from source code requires special tools, not just a compiler + xcopy -- your situation is conceptually no different from teams who share a common utility library.

Luckily, this is a very well researched topic. Furthermore, there's nothing particular to TFS about it; you can read the documentation for any source control system with robust branching & merging functionality, which by now is most of them. Practical Perforce, the Red Bean book (SVN), Eric Sink's blog (Vault), etc all have good insights. I'm particularly partial to Laura Wingerd's presentation on codelines. Naturally you should read the latest TFS guidance too.

Here on StackOverflow, the question of putting those concepts into practice has also come up several times. This one is the best overall summary for TFS users: http://stackoverflow.com/questions/381020/team-foundation-server-source-control-structure It incorporates the most important industry principles...

1. all branches are self contained. no dependencies allowed between branches, or between a branch and a fixed (non-branched) location
2. relative paths within branches are invariant
3. promotion model is well defined and applies equally to all engineering artifacts: development -> integration -> production (to use their terms for the primary codelines; many others are in common use, usually amounting to the same core idea)
4. 1 Integration branch (aka Trunk, aka Main) that links the stable & unstable sides of the tree. no peer-to-peer merging allowed.
5. variable # of Dev branches depending on the degree of isolation needed between teams/features/refactors
6. variable # of Release branches depending on the frequency of hotfixes, whether any releases overlap, and how strict the auditing requirements are
7. if you choose to checkin secondary resources like documentation, 3rd party binaries, compilers, etc, they belong inside the branch structure. see rule #1.

...along with some TFS specific quirks...

• don't use workspace mappings to hack your way around shared files. (dunno who first wrote that "guidance" -- luckily it no longer appears in the latest P&P revision)
• don't branch/merge into a subdirectory of an existing branch hierarchy. (again, some "advice" that's followed TFS since its debut, even though I have yet to meet someone who was happy they followed it)
• don't use the default TeamBuildTypes folder; like all code, your build scripts should follow points #1-3 from above

(Frankly, though, the responses there have gotten a little too comprehensive. Even if you have dozens or hundreds of branches, there's no need to nest them as deeply inside the source tree as the revised question does. Having some branches live deeper than others is especially confusing, IMO obscuring the key takeaways from readers who are new to large scale source management and/or path-space branching in general. Simplicity is golden if you hope to get everyone in your organization to follow the "rules of the road," as Wingerd phrases it.)

Anyway, I know you weren't asking about branching & merging specifically, but the way you lay out your source tree has direct implications for your overall software process. Bluntly, if you don't follow rules like #1 when you add the database project, your front-end app teams will never, ever be able to function independently. As such, your first proposal (the structure pictured under $/FrontOfficeDevelopment) is much closer to the mark than the second. But you need to go farther. Move the folders for the 3 apps + database one level deeper in the tree. I.e., give them a common parent -- let's call it "Integration" to match the other StackOverflow example. Should you ever need to branch, you can now do so in one self-contained action by branching this container; would be much harder if each app was a top-level folder in the Team Project. Before long your source tree will look just like the ideals pictured in the "TFS Guidance II" diagrams...no coincidence :)

$/Everything
   |- Integration
       |- 3rdPartyAssemblies
       |- Database
       |- OnlineOrders
            |- Code
            |- Tests*
       |- ReportingSuite
            |- Code
            |- Tests
       |- TeamBuildTypes
            |- TfsBuild.proj
            |- QuickBuild.targets
            |- FullBuild.targets
            |- FullBuildAndTest.targets
       |- TradeManagement
            |- Code
            |- Tests
   |- Development #1
       |- 3rdPartyAssemblies
       |- Database
       |- etc
   |- Release #1
       |- 3rdPartyAssemblies
       |- Database
       |- etc

*Breaking up your tests per-app, as shown above, makes it easier for individual teams to work on their slice of the tree. The OnlineOrders guys only need to download OnlineOrders + shared stuff like 3rdParty & Database, which is convenient if your apps are very large or there are dozens/hundreds of them. However, it's equally valid to make one top level Tests folder inside the branch as shown below:

   |- Integration
       |- Database
       |- OnlineOrders
       |- ...
       |- Tests
            |- Database
            |- OnlineOrders
            |- ...

This makes it more convenient to run your entire suite of tests at once and reduces the overall depth/complexity of the tree. Downside is you'll have to navigate around the tree more often during everyday work. Perhaps more troubling long-term, it requires you to maintain a parallel structure by hand. Adding/removing projects, code refactoring, departmental reorgs, outsourcing -- lots of things can change the layout of the main code folders over time. If Tests isn't updated to match, you'll have confused QA people at minimum, and a decent chance of broken test automation to boot.

---

You also raised the question of whether to divide your company's efforts into Team Projects. The good news is that this decision is totally orthogonal to the branch/merge process you choose. Unlike builds, reports, Sharepoint artifacts, and (to some degree) work items, which are all tightly coupled to the notion of Team Projects, the TFS source control system is simply one big tree that spans them all. I happened to use an "Everything" project in my diagram because it was easier to draw -- and because I'm admittedly partial to that strategy myself -- but it actually doesn't matter.

Nevertheless, mating what we've learned so far to the TFS concept of a "Team Project" requires some extra thought. I'll admit it's not apparent from a cursory look at the product what the heck a "Team Project" does in the first place. Suffice to say they are intended to be really big containers. Let's pick some familiar examples. Windows and Office should definitely be separate Team Projects -- they are developed on independent release schedules, using very different engineering practices, running heavily customized bug & reporting workflows, totally incompatible build systems, etc etc. However, there's little reason to separate the NTFS team and the MSPaint team into separate Team Projects even if their actual work never overlaps; nor should Windows 7 SP1 and Windows 8 development be split, necessarily, unless the next release milestone is also bringing major process changes along with it.

As with branching, I feel simplicity is golden. Once you have multiple Team Projects, many things that were once mundane tasks suddenly become tough decisions. Let's say you find a bug in a database sproc that's primarily developed by another team. Do you open the bug in your team project (to ensure it's resolved back to you for QA signoff), in the team project of the other team (since they'll be the ones coding the fix), or in a special DB-only team project? How many places is the average developer expected to search for active work items, anyway? That's just the first scenario that popped into mind; there are many more features that don't translate well across projects. Unfortunately, there are also some features that require you to split things up. If team #1 wants to use just-in-time merging but team #2's development process relies on exclusive checkout locks, that's simply not supported within a single team project.

I've used the word "process" a few times now. That's really what it comes down to. Once you understand the concept of a TFS Process Template, Team Projects make a lot more sense. Here's an old blog post where I expand on this idea, leading to a handy rule of thumb: create one team project per process template. (Of course there are exceptions; not all TFS features can be controlled by templates.) More details are available in the whitepaper that prompted my post -- inside you'll find a chart detailing exactly what is/isn't supported within/between individual team projects. My guess is a small shop like yours won't require many compromises to fit the single Team Project model, if any.

Answer 4

For anyone still interested in how we ended up doing it: Got a TFS expert in to help!

We plumped for this: Split all solutions our into their projects, put all the projects into related buckets, have the .sln files in the Source directory so they were easy to find. It was a logical way to organise things and has worked so far.

<Branch>
Source
    Solution files at root
    Server Apps     Previously “services”, helper apps running on server
        App 1
            Project 1
            Project N
            Installer
        App 2
    Services    WCF/Web services providing business operations
        App 1
            Project 1
            Installer
    Client      Rich client applications, e.g. WinForms, WPF
        App 1   
            Project 1
            Installer
    Web     Server side web applications
        App 1
            Project 1
            Installer
    Database    All scripts related to database structures and data
        Databases
            SMOL
            AuditAuthentication
            …
        Servers (Environments?)
            Server1.proj
            Server2.proj
    Common  Reusable classes across applications
        Communications
        Security
        ….
    SharedBin   3rd party binaries
        EnterpriseLibrary
        …
Docs
    Release notes
    Help files
Scripts
    Deployment
    Data Migration
    …
System Tests
    Functional
    Performance
    Security
    …

Here's what it looks like in VS (some obfuscation to protect the innocent)