Source Controle structure

[Guest]

We're currently evaluating Vault and, as part of this exercise, we're attempting to design a structure that fits our company's applications and databases. We have a number of in-house developed applications each with their own SQL Server databases. The apps make a lot of use of SQL Stored Procedures and views that exist in other apps's databases. In other words, the databases interface together quite a lot. Our aim is to keep all database 'source-code' (ie. .sql scripts) in source control. Getting on to the question now... Given the dependencies between the databases, should would we be advised to have one directory 'Databases' for all application database objects like...

Option 1
$/
.......Frodo/
..........trunk/
.............src/
.............doc/
.............tests/
..........branches/
.............blabla/
................src/
................doc/
................tests/
.......Sauron/
..........trunk/
.............src/
.............doc/
.............tests/
..........branches/
.............whoops/
................src/
................doc/
................tests/
.......SQLDatabases/
..........trunk/
.............Tables/
.............Sprocs/
.............UDFS/
.............Views/
..........branches/
.............gratuitous_rewrite/
................Tables/
................Sprocs/
................UDFS/
................Views/
...

Or would it be better to separate the databases according to the associated application? eg.
Option 2
$/
........Frodo/
..........trunk/
.............src/
.............doc/
.............tests/
.............SQLDatabases/
................Tables/
................Sprocs/
................ UDFS/
.................Views/
........branches/
.............blabla/
................src/
................doc/
................tests/
................SQLDatabases/
....................Tables/
....................Sprocs/
....................UDFS/
....................Views/
........Sauron/
...........trunk/
.............src/
.............doc/
.............tests/
.............SQLDatabases/
................Tables/
................Sprocs/
................UDFS/
................Views/
..........branches/
.............whoops/
................src/
................doc/
................tests/
................SQLDatabases/
...................Tables/
...................Sprocs/
...................UDFS/
...................Views/


Option 2 looks logical but Option 1 seems to offer better source control potential in terms of ensuring that changes in one database don't break other applications. What's the best way to go here?

Thanks in advance,

Clive Richardson[/list]

[jclausius]

Will any tables / functions / stored procs etc be shared between applications? If not, then option 2 would be the best.

However, if you have a common library of SQL objects which are common throughout, you might be better off using Option 1's structure, and then sharing in either a common folder ( if there are more than 2 scripts ) or just the common files you wish to use in the projects.

Note... Sharing is not the best option if libraries are in a state of flux, and this introduces problems that may "break" a part of the tree. In these cases you can pin the share or also look to branching, as it may help keep things under control.

[crichardson]

Thanks Jeff,

> Will any tables / functions / stored procs etc be shared between
> applications? If not, then option 2 would be the best.
There is a fair amount of 'sharing' - to me 'sharing' in this context means that an application or stored procedure can sometimes reference an object in another database. I'm not sure that 'sharing' is the correct terminology for this because a database's objects are definitely owned and controled by the project for that application/database. So, it's not so much 'sharing' as it is 'referencing'. The point is that a change to one database is probably not best dealt with in isolation.

>However, if you have a common library of SQL objects which are
>common throughout, you might be better off using Option 1's structure,
>and then sharing in either a common folder ( if there are more than 2 >scripts ) or just the common files you wish to use in the projects.
We don't keep a common library of SQL objects. Currently, each database is treated separately and problems occur because of this (but we don't really have any real source control or config mgt processes at the moment). I understand what you've said about being better off using Option 1's structure in these circumstances but I'm not sure what you meant by 'and then sharing in either a common folder' and so on? Doesn't Option 1 already consist of a common folder for the databases objects?

>Note... Sharing is not the best option if libraries are in a state of flux,
>and this introduces problems that may "break" a part of the tree. In
>these cases you can pin the share or also look to branching, as it may
>help keep things under control.
My understanding of pinning and sharing is limited at this stage but I can't see how pinning a shared file would help in our case because if an object in database A is referenced by application B using database B, if the referenced object in db A is changed, whether the source is pinned or not, Application B will probably fail. What we need is a build process and underlying Source Code repositiory that will allow for cross-database dependencies. I am still not sure of the best way of achieving this.

Clive

[jclausius]

Usually, most objects do not reference items outside their own database. However, I know there are some situations where this is useful or unavoidable.

In cases like these, I think I would create branches of the "dependent" databases as sub directories of the main project. This way, the sub-project database is kept at a known state. Assuming changes are made to the main dev branch, the sub-project is at a known state and can be updated with a Merge from the main dev branch.


I see know what you were asking, and sharing is probably not the way to go here. Branching should do nicely. In any case, I've addressed your other questions below.

There is a fair amount of 'sharing' - to me 'sharing' in this context means that an application or stored procedure can sometimes reference an object in another database. I'm not sure that 'sharing' is the correct terminology for this because a database's objects are definitely owned and controled by the project for that application/database. So, it's not so much 'sharing' as it is 'referencing'. The point is that a change to one database is probably not best dealt with in isolation.

No, that is not what I meant by sharing. What I meant is something like the following: Imagine a user defined function (UDF), which takes a comma delimitted string, and then returns a table of each item. This UDF would be beneficial in many different databases ( or perhaps in one common database ). So if you wanted the UDF in multiple databases, you would have the .sql definition file shared in each project. Also, you would need a project specific file which can take the .sql definition and run the CREATE FUNCTION within each database.

We don't keep a common library of SQL objects. Currently, each database is treated separately and problems occur because of this (but we don't really have any real source control or config mgt processes at the moment). I understand what you've said about being better off using Option 1's structure in these circumstances but I'm not sure what you meant by 'and then sharing in either a common folder' and so on? Doesn't Option 1 already consist of a common folder for the databases objects?

Extending my example above, let's say you have a UFN which calculates the Time-Value of Money. Also you have one which creates a CRC number, and one to calculate an MD5 value. With these UDFs in hand, you are creating a "shared" library of UDFs which could be useful in more than one situation.

So instead of sharing each individual .sql file, you could create a Shared Folder which contains the .sql files. The folder is the object which is shared. So when you add the next .sql file to this shared folder, the file is automatically seen in ALL projects where the folder is shared.

My understanding of pinning and sharing is limited at this stage but I can't see how pinning a shared file would help in our case because if an object in database A is referenced by application B using database B, if the referenced object in db A is changed, whether the source is pinned or not, Application B will probably fail. What we need is a build process and underlying Source Code repositiory that will allow for cross-database dependencies. I am still not sure of the best way of achieving this.

Sorry if this has muddied the water. Database Dependency was not what I was solving in my solution. It was just a way to share some common .sql files across database projects.