database reusability with sql server

Hi,

The company I work for often develops databases for clients. Those
databases always need to do similar tasks. One company might need a
database to do task 1 and task 2, while another company might need it
for task 2 and 3. Instead of having to redevelop each database from
scratch, I'd like to be able to develop several "modules" (kind of
mini-databases) representing each of this task, and then chose the
modules which are relevant. Then all I would have to develop would be
the final layer putting together all the information gathered from
those modules. I'm sure this has been done heaps of time before but I
can't really find any information on it. Any of you have any pointers
or experience you would like to share. I'd like to have a good think
about how I'm going to go about it, and the limitations that might
arise before I get stuck into it.

Thanks for your help,

MaelleYou could do this in several ways, depending on your exact requirements and
the tools you have available. Are you going to put all your modules in every
client database, for example, and only 'activate' the ones that client
really needs? Or do you plan to set up a different code base for each
client, based on a different selection of modules every time? (The second
approach could become very complex to manage to support.) Will you have a
common module with global data and code to be used in all other modules?
Etc.

Either way, if you want to 'modularize' your code, it's really up to you to
track dependencies carefully, and ensure you always know exactly which
objects go into each module. Some tools can help with this, e.g. Erwin
allows you to split your data model into functional areas, and you can view
them separately or together. In your source control system, you would group
related tables/procs/functions etc. together.

A good naming convention can also help, perhaps by giving related objects a
prefix to indicate that they are related. You will have to look at the
resources you have available, and how you want to manage code once it's been
deployed - it's difficult to give a more specific answer, as you know your
own application best.

Simon

"Maellic" <maellic2002@.yahoo.fr> wrote in message
news:15aa032f.0306231501.5af14872@.posting.google.c om...
> Hi,
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.
> Thanks for your help,
> Maelle|||Maellic (maellic2002@.yahoo.fr) writes:
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.

We are building our product in this fashion. Admittedly the core is still
the largest building stone, but the idea is that a customer that does
not need the XYZ functionality does neither get the tables or the stored
procedures for it.

We've found that this is fairly easy to do with new development, but
as I mentioned the core is fairly large, and if we ever manage to
break it up remains to see. The difficult thing is to get the
dependencies right. If you have modules A and B, you can permit
references in one direction, but not in two.

What we have put quite some effort in, is to have a tool that supports
this concept for installation and updates. I don't know if this is an
issue for you, but we regularly send new versions of our product to
our customers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DataBase reusability

Hello,
I am developing several database centric applications.
Each application has gotten one or more databases. I
discovered that some applications share some
functionalities around database tables. For example,
Application A will query the Members, Groups,
MemberPreferences tables in database A, and Application
B queries the Members, Groups, MemberPreferences tables
in database B. Those tables named Members, Groups, and
MemberPreferences have the identical table schema, and
have the same select/insert/update/delete stored
procedures, and triggers.
Is there any good solution to maintain those kind of
databases easily? Using the data import/export provided
by the SQL Enterprise Manager might help a bit. Any
other efficient alternatives to synchronize the table
schema and stored procedures among those databases?
W. JordanW,Jordan
Are there any reasons to have two databases if as you said they have the
same table's structure?
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'
"W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>|||check out DB Ghost (http://www.dbghost.com)
"W. Jordan" wrote:

> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>
>|||Hello Uri,
Yes, the database items with similar structure are used by different
applications, although, which share some identical behaviors.
We are developing several applications on a single centric machine.
For portability on a development machine, I obviously can not have
those applications sharing one table in one database since the data
will get conflicted.
Using the master table is a trick for stored procedures. But, is it a
good practice to do so?
Best Regards,
W. Jordan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RGY3LWHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: W,Jordan
: Are there any reasons to have two databases if as you said they have the
: same table's structure?
:
: use master
: create table t(c1 varchar(50)) insert t values('master')
: go
: create proc sp_test as select * from t
: GO
: use northwind
: create table t(c1 varchar(50)) insert t values('northwind')
: use pubs
: create table t(c1 varchar(50)) insert t values('pubs')
: use pubs
: exec sp_test --returns 'master'
: use master
: exec sp_MS_marksystemobject sp_test
: use pubs
: exec sp_test --returns 'pubs'
: use northwind
: exec sp_test --returns 'northwind'
:
:
:
: "W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
: news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: > Hello,
: >
: > I am developing several database centric applications.
: > Each application has gotten one or more databases. I
: > discovered that some applications share some
: > functionalities around database tables. For example,
: > Application A will query the Members, Groups,
: > MemberPreferences tables in database A, and Application
: > B queries the Members, Groups, MemberPreferences tables
: > in database B. Those tables named Members, Groups, and
: > MemberPreferences have the identical table schema, and
: > have the same select/insert/update/delete stored
: > procedures, and triggers.
: >
: > Is there any good solution to maintain those kind of
: > databases easily? Using the data import/export provided
: > by the SQL Enterprise Manager might help a bit. Any
: > other efficient alternatives to synchronize the table
: > schema and stored procedures among those databases?
: >
: > W. Jordan
: >
: >
:
:|||Hello Mark,
DB Ghost seems to be able to solve my problem.
But I think that is not a thing we can afford.
Best Regards,
W. Jordan
"mark baekdal" <markbaekdal@.discussions.microsoft.com>
wrote in message
news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
: check out DB Ghost (http://www.dbghost.com)
:
:|||Hello Welman.
the question could also be "Can you afford not to have it?". What ever you
decide thanks for looking.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Welman Jordan" wrote:

> Hello Mark,
> DB Ghost seems to be able to solve my problem.
> But I think that is not a thing we can afford.
> --
> Best Regards,
> W. Jordan
>
> "mark baekdal" <markbaekdal@.discussions.microsoft.com>
> wrote in message
> news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
> : check out DB Ghost (http://www.dbghost.com)
> :
> :
>
>