Database projects have been around for a while, using Visual Studio, but they were semi-clunkers, and people avoided Visual Studio, and preferred SQL Server Management Studio, for most of their database related work. With the advent of SQL Server Data Tools (SSDT), it became a much smoother ride. Less rigidity, more flexibility.
The older Visual Studio Database Projects were knows as Data Dude projects, named after Gert Drapers, the original data-dude, according to Bob Beauchemin (SQL Skills) . The Data Dude exemplar suffered some deficiencies, which were addressed by SSDT, the Visual Studio in-house replacement for SQL Server Management Studio , aimed to reduce the difficult database deployment of Visual Studio.
New Data Tools Data Project
Visual Studio 2012 comes with a new template: SQL Server, which is the SQL Server Data Tools (SSDT) version of a database project.
Once a database project is opened from the template, a minimalistic project structure presents itself. It does not create a boiler-plate folder structure at all.
To experiment with a structure, any existing database can be imported, to see how Visual Studio 2012 organises it.
Import an Existing Database
SQL Server 2012 Database Folder Structure
Choosing a folder-structure, for the SSDT database project in Visual Studio 2012, is important from a familiarity, readability, and maintainability point of view. The structure has no bearing on the deployment, and does not influence anything from a database point of view.
The folder-structure, as seen from the [Object Explorer] tab, in SQL Server Management Studio (SSMS) is still very much the same-olde. Because Visual Studio has no bearing on it, the two environments could be diametrically opposed in their structures.
It will probably make a lot of sense, to mimic the SSMS structure, from a familiarity perspective, if the database design is not heavily schematic.
Choosing a Folder structure, from the Import Database wizard, provides a template, according to which Visual Studio arranges database objects.
Obviously, where nothing, or None is chosen, no folder structure is created. A blank project leaves it up to the project team to define the structure. An import of an existing database (where no structure is chosen, or defined), will dump all files in the root of the project, which is moderately messy, so it must be carefully considered.
Importing an Existing Database
The Import Database wizard, accessed by right-cliking the database project, and following the path: Import >> Database, is used to choose a folder structure.
Import an Existing Database
The wizard provides four options for a project folder-structure, viz. None, Schema, Object Type, and Schema Object Type. Because None, is the default of a new SSDT database project, it is not treated in the list below, but in the next section.
Making a folder-structure choice, really depends on the characteristics of the database project, more specifically what the visual presentation of database objects should conform to, from a organisational perspective. Within each option, the rationale for choosing it, is discussed.
NOTE: Where a database is imported, let’s say that has no Stored Procedures, the folder for it is also not created. When such objects are subsequently added to the project, it would necessitate that the folder first be created. This could infringe upon the project standards for database development, where such exists. If no database coding standard exists, there would be little guidance, and projects could diverge greatly, from a standardising and conforming perspective.
The Schema import option for the folder structure, imports all dbo objects into the folder structure: <solution> >> <project/ database> >> <schema|[dbo]>. It stores all objects connected with a schema, in a corresponding folder.
Schema Folder Structure
Schema separation for database objects, should be considered where the database is heavily schematic, i.e. object split into schemas.
Interestingly, this option exercises some intelligence, by grouping like-named objects into sub-folders, naming them Tables1 . . . n, for example.
This seems a bit counter-intuitive, because the grouping of database objects, is an Object Type function, whereas this option is Schema, or seems to indicate Schema only. It could be, where the import wizard identifies objects starting consistently with the same text (indicative of some kind of grouping), it automatically assumes a differentiation according to Object Type.
This might be irritating, depending on whether or not it is appreciated, from an organisational perspective.
Often, objects are named by providing a prefix, denoting a grouping that causes the sorting of the objects in the folder, accordingly. If Visual Studio provides an intuitive grouping, through the Schema Object Type import option, it overstates the obvious, by placing all like-named objects in sub-folders, something already achieved through the sorting of the names.
It does, however, stay in tune with the dbo (schema) folder idea. Where other schemas are created, these are all stored in their own folder structures, following the convention of storing all similarly named objects in their corresponding sub-folder structures, within their schemas.
It also seems, that where objects cannot be categorised, in accordance with the schema, or type, or and naming convention, they are dumped under the schema folder root. These could be anything, ranging tables, views, functions, and stored procedures. It’s relatively easy to remedy this, after the import (given the size of the project, i.e. the number of objects) by manually creating folders for the skipped objects, and dragging the artefacts into those folders.
The Object Type import option for the folder structure, imports all objects by Object Type, placing them in a folder structure.
Object Type Folder Structure
This structure follows the SQL Server database folder structure, as displayed through SQL Server Management Studio. The choice between this option and the next (Schema Object Type) is probably the one through which a database project should be defined.
Where a database is not heavily schematic, specialised into schemas, it would not be required to provide such a level of separation. By looking at Objects, might be the easiest way to sort, and find them.
Where a database is very large, and separated by various schemas, the additional level of separation, makes finding artefacts easier, and this type of folder structure, i.e. Object Type, may not be as suitable.
The Object Type folder structure may thus be more suitable for databases with a singular schema definition.
Schema Object Type
Folder Structure Schema_Object Type Sub-Folders
The Schema Object Type import option, for the folder structure, imports all objects in similar fashion to the previous data-dude folder-structure for Visual Studio.
As the name suggested, this is a combination between schema, and object type. For example, database objects are sorted into sub-folders, in accordance with similar naming conventions. NOTE: Any unidentified artefacts, are dumped under the schema folder, similarly to the Schema option above.
Choosing this folder structure, for the database project, would be done where a project is large, and specialised into a few schemas, probably also according to Object Types, having them split into sub-folders, according to their systems, by providing indicative naming conventions.
NOTE: This discussion does not explore how folders are created, when importing another (previous) database project from an earlier Visual Studio version. There is a MSDN.social discussion about this, titled: SSDT project structure after conversion from VS 2010 DB project .
Creating a Database from Scratch
Blank-slate for Database ACMECorporation
To create a database from scratch, should incorporate the same thought processes (on the folder structure of the database project) as when importing an existing database,and for the same reasons.
When presented with a blank-slate, it does not propose going ape.
It’s intuitive that sticking with a folder-structure definition, like that seen through SSMS, is probably the better option, bar the reasons explained in the respective folder-structure options, above.
The reason that a blank-project starts with no folder structure, is probably because Microsoft does not know (and don’t want to propose they do) what kind of project (database) is being envisaged, or imported. Rather than casting a structure in stone, which is more often than not counter intuitive, it is left up to the project team to define.
A problem (one that is not new) is that the folder structure in SSMS is not conducive to multi-schema systems, in that it does not provide a Schema Object folder structure, for ease of reference. Visual Studio to SSMS alignment is only possible where the Object Type folder structure is chosen. It is probably the one that would be chosen by most database professionals.
Visual Studio 2012 (SSDT) provides options for creating a database project folder-structure, when importing an existing database, or Visual Studio Project. However, when creating a project from scratch, it starts with no folder structure.
It seems intuitive to follow the structure proposed by SQL Server Management Studio, for a database, keeping the project’s structure as familiar as possible. However, a various schematic-database, might benefit from a schema specialised folder structure, and in that instance be divergent from the normative SSMS folder structure.
Last Updated: 12 November 2012, 13h04