Develop more quickly by re-using databases

by Arthur Fuller | Jun 06, 2005 7:00:00 AM

Tags: Storage, Databases, OOA/OOD/OOP, Arthur Fuller, database, object-oriented, SQL Server Newsletter

Takeaway: Arthur Fuller explains why SQL Server developers should take a page from object-oriented programmers' way of thinking of about reusability. He also discusses how re-using databases can reduce redundancy and lead to amazing gains in development time.

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!

4 comment(s)