Worth the headache to organize SQL files by application subject?

Question

At my company, we save each database object (stored proc, view, etc) as an individual SQL file, and place them under source control that way.

Up until now, we've had a very flat storage model in our versioned file structure:

• DatabaseProject
  • Functions
    • (all functions here; no further nesting)
  • StoredProcedures
    • (all stored procs in here; no further nesting)
  • Views
    • (ditto)

For a big new project, another idea has occurred to me: why not store these files by subject instead of in these prefab flat lists?

For example:

• DatabaseProject
  • Reports
    • (individual stored procs, views, etc.)
    • SpecificReport
      • (more objects here, further nesting as necessary)
  • SpecificApplication
    • (all types of DB objects, with arbitrarily deep nesting)
  • et cetera....

The obvious flaw is that this folder structure doesn't impose any kind of namespace hierarchy on the database objects; it's for organization only. Thus, it would be very easy to introduce objects with duplicate names. You'd need some kind of build tool to survey the database project and die on naming conflicts.

What I'd like to know is: has anyone tried this method of organizing SQL files by application subject in their versioned file structure? Was it worth it? Did you create a build tool that would police the project as I have described?

Answer 1

You should define a naming scheme for your database objects, so that it's clear where a view or SP is being used.

This can either be prefixes to describe the app modules, or separate schema names for modules/functionality.

No nesting required, and names in the VCS show up the same as in the database, and sort properly depending on the naming scheme.

Answer 2

We save our SQL files in a "SQL" solution folder with each project. That way, each project is "installed" separately.

Answer 3

I like to have my SQL scripts organized by topics, rather than by name. As a rule, I even group related items into single files. The main advantages of this are :

• You do not clutter your filesystem/IDE with files (many of them being a few lines long).
• The overall database structure shows more directly.

ON the other hand, it may be more difficult to find the source code related to a specific object...

As for duplicate names : it can never happen, because you obviously have automated scripts to build your database. Relying on your filesystem for this is looking for trouble...

As a conclusion, I would say that your current rules are much better than no rule at all.