What place in TFS should I put my database project in?

Question

Have looked through the other questions and can't see a clear answer to this.

We are a small development team, working on what could be described as 3 separate front-office 'applications' (OnlineOrders via asp web, TradeManagement winforms app, ASP.NET ReportingSuite).

However for better or worse, each of these apps share one central SQL2005 database, let's call it MainDB. They all use the same Orders table structure, the Users, the Accounts etc., pretty much 80% of the objects are used in some way by each of the apps.

In TFS, I had thought that each of these 3 apps as being seperate Projects, with the Work Item tracking, reporting etc that would give us. Seemed to work fine getting our 'code based' Solutions created.

Now I'm trying to see how I can get the MainDB database into TFS Source Control. I can't see an obvious way to do this.

Question is: Am I supposed to create a database project for MainDB, export the schema into scripts in the project, and add that database project into each Solution, inside each TFS Project?

Or should I have a separate TFS Project just for my database project? And a developer would have to open both the MainDB database TFS Project and (eg) the TradeManagement TFS Project open to develop a new feature (as most new features will involve some db changes too)? Seems very heavy handed.

Or is it common to just have one massive TFS Project, called 'Everything', and within that have feature branches for the code projects, and a database branch for the database, and any other projects that might use the MainDB database? (in the img, assume MainDB scripts are in the 'Database' folder)

etc. Man, I am confused. Codeplex seems to point to keeping it simple by putting all solutions inside on big single solution. Is that sustainable?

---

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 1

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: 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. (I don't know 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.