Solutions by Design: Data Modeling 2005

SQL Server 2005 introduces a host of new features and concepts: new server tools, new T-SQL language constructs, and even a totally new look and feel to our familiar old Enterprise Manager, which is being replaced with the new SQL Server Management Studio.The business intelligence (BI) interface has been reworked, DTS has been replaced with SQL Server Integration Services (SSIS) and has been made more secure.You don't have to write stored procedures only in T-SQL anymore, thanks to Common Language Runtime (CLR) integration, and the new SQL Server Report Builder is going to make data presentation, well, fun.

Yet given all these new toys and tools (many of which are described in detail in other SQL Server Magazine articles or other resources cited in the Related Reading box), is anything really changing for the data modeler? The quick answer is no. Despite all these new features and functionality, one thing hasn't changed—you still have to model the data before you build the database.

Data modeling is the process of capturing business rules, regulations, and requirements and converting them, correctly interpreted, into a well-structured set of database tables, relationships, and constraints.You express the model with a series of graphical displays in an effort to better understand the data and how it affects organizational operations. These graphical displays aren't limited to any specific database platform. You can develop a conceptual data model, and subsequently a logical data model, that you can implement on any number of database management systems (DBMSs)—SQL Server, Oracle, IBM DB2, or MySQL, to name a few.Therefore, version upgrades in a DBMS don't change how you go about the job of understanding the data and the business requirements and creating the data models.

CASE and SQL Server 2005

Data modelers use CASE software to model data. CASE software supports not just database design, but the entire application development life cycle. It lets you create a Requirements Model, which contains the business rules, regulations, and requirements in text form.You can map portions of the Requirements Model to multiple other types of design objects, such as Business Process Models, Conceptual Data Models, the many diagrams that are part of object-oriented (OO) modeling, XML Models, external files, or even to users and groups who have to meet these requirements during the development process.The CASE software retains its links to the original requirements while you create multiple levels of models, maturing to the production physical model stage and beyond into live implementation.Typically, you store these links and all the CASE data in a commonly accessible repository database.

A CASE tool also lets you create Information Liquidity Models to specify, document, and implement data transfers by using replication.You can run Impact Analysis Models to determine dependencies between tables, columns, views, stored procedures, and so on and to simulate the impact on your production system if you were to change or remove an object.You can create reports that can be published electronically to the Internet or printed. Really good CASE software can break a large model into submodels or packages so that multiple data modelers can work on the model at the same time without anyone unintentionally overwriting anyone else's work.

Not every CASE software package can do all these things. But if your CASE software has even a subset of these capabilities, you know how much of a time saver CASE software can be.Without it, you're relegated to creating model documents by hand, if you create them at all. And without some sort of database documentation, you're essentially flying blind—you have no idea whether the database even begins to meet your business requirements or whether the applications that run on top of the database are maximizing data utilization.

If your company has a large development team and a generous budget and is considering an investment in Visual Studio (VS) 2005 Team Suite, you might explore the CASE capabilities of the VS 2005 Team Suite's VS 2005 Team Edition for Software Architects component, which includes the Service Oriented Architecture (SOA) Modeling module and the Deployment Design module. If you're a sole practitioner and you don't need the additional programdevelopment and code-testing capabilities of theVS 2005 Team Suite, you might want to choose a different CASE package. Sybase's PowerDesigner, Computer Associates'(CA's) All-Fusion ERwin Data Modeler, and Embarcadero Technologies' ER/Studio, just to name a few, all integrate with SQL Server. And, of course, there's SQL Server's own DaVinci toolset, also known as the Database Diagrammer.

I haven't tested all these tools yet for SQL Server 2005 compatibility, but I did call David Dichmann, PowerDesigner product manager, who told me that PowerDesigner 12, which is in public beta (beta 2), already incorporates many of SQL Server 2005's new features, including enhanced support for catalog and metadata views, and view options such as encryption and schema binding. PowerDesigner 12 includes the Transact-SQL commands for opening and closing a database as part of the code-generation feature and incorporates changes to the catalog for proper reverse engineering. The current beta has support for the new varbinary(max) data type, and the XML data type that was missing from beta 1 has been reported to the product team. At the time of this writing, PowerDesigner 12 just entered beta 2; some new features that are currently missing still have a good chance of making it into the final product. Data modelers whose companies are moving to SQL Server 2005 should be sure to check whether their CASE software supports the new database features.