Depending on your job specs, you may work solely with SQL
Server, or you may use SQL along with various front-end applications, written
in .NET, Java, or Delphi. In such languages, you are accustomed to the concept
of reusability. However, SQL developers rarely (if ever) think of re-using
databases.

Consider this: You write the same thing over and over—from
customers to sales orders to purchase orders and so on. That’s the atomic
level, but you can abstract this redundancy to even higher levels, such as the
modules you typically write for an accounting system: Accounts Receivable,
Accounts Payable, Inventory Management, etc. Each module requires a group of
tables that don’t vary much from customer to customer, or database to database.
So why not design them as separate databases?

In part, the answer is deadline pressure. Developers seldom
have time to abstract the common objects in our databases. This is where we
could learn something from our object-oriented colleagues; they have persuaded
management that reusability is both architecturally valid and economically
sound. And yet, most of us in the database development and management trenches
have not done the same.

It’s time we begin thinking about database development in
these terms. On a basic level, customers, vendors, and employees have a lot in
common. One level up, the way you model addresses (cities, regions, countries)
remains pretty much the same despite the particular database. One more level
up, sales-order-entry is essentially the same regardless of what you sell.

We can rise above the specifics of a database design in the
same way that our object-oriented colleagues have risen above the specifics of
a user application. The biggest step in this process is simply deciding that we
can do it. Once I realized that the same principles applied to database design
and object-oriented design, it only took me about two weeks to load my existing
projects into a data-modeling tool and abstract the useful portions. Using the
same tool, I can now quickly import simple (i.e., customer table) and complex
(i.e., Accounts Receivable) objects into any new database that I create. The
gains in development time are amazing.

Two ways to go about re-using databases

There are two ways that you can approach this transition.
I’ll present with you the pros and cons of each method, but I’ll leave it up to
you to decide which approach will work best for your situation.

The first approach is to connect to numerous databases, each
containing the tables of interest to the particular “object” of interest (such
as Accounts Receivable). This means that your production database contains
views, stored procedures, and UDFs that address the
other databases so the client programs see just one database. The advantage to
this approach is that, if you change an object within the Accounts Receivable
“object”, the virtual database will “inherit” the change. The downside to this
approach is that foreign keys don’t work across database boundaries; therefore,
you must use triggers, which are slower.

The second approach is to import the “object” databases into
your current project. The upside is that you can use foreign keys rather than
triggers. The downside is that if you change one of the objects, such as the
Accounts Receivable model, then you will have to propagate that change
manually.

I’ll reiterate that you can gain a lot by thinking about
your databases as objects. You don’t need an
object-oriented database to make this transition—you just need a change of
perspective.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!