database reusability with sql server

[Maellic]

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

[Simon Hayes]

Re: database reusability with sql server

You 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@ya hoo.fr> wrote in message
news:15aa032f.0 306231501.5af14 872@posting.goo gle.com...[color=blue]
> 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[/color]

[Erland Sommarskog]

Re: database reusability with sql server

Maellic (maellic2002@ya hoo.fr) writes:[color=blue]
> 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.[/color]

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/techinfo/productdoc/2000/books.asp