Restricted Design Restricts Business Operations

In order to get it right the first time, you have to understand the business requirements. Such was not the case on a project that involved a complete redesign of a production database that supported an Internet retail organization. In this case, the company had four Internet retail websites with a plan to increase this number and possibly evolve the business to a franchisebased operation. The four retail sites all relied on a shared database and application code base.
Due to how the database schema was constructed, early database design decisions were hampering organizational growth.
The database schema, designed by a third party, had served the company well to this point but failed to take into consideration the going-forward business plan. Not being technical people, the management group never recognized these design shortcomings which led to hard-coded limitations on the number of stores and the type of services that this company could offer.
Without fundamentally restructuring the underlying database and rewriting the code base, there was no way to expand the features and functionality of this database system to support business growth initiatives.
For this kind of situation, you should create a checklist for yourself of things to do and things to consider. First on the checklist is to identify the business requirements, both current and future.
You'll want to incorporate these into the new database schema. Enlist the help of end users to ensure that your database design will be able to support their needs and expectations. In this case, since this was a retail database, we studied the behavior of real-life shoppers in a brickand- mortar store, and translated that into the database schema, adding features that were unique to an online shopping experience.
Second on the checklist is to look at the physical implementation, specifically the way the database files have been placed on disk. Using a tool to provide proactive monitoring and management of the database is necessary. In this case, I used an operational profiling tool to test performance and query response both before and after the physical restructuring. My findings before restructuring were not encouraging. The original database had all objects stored in a single file group. This database included all types of data — tabular (numbers, short character strings, dates/times) and text/image (long product descriptions and LOTS of images), and that single file group was located on one physical/logical disk. This configuration adversely impacted performance and degraded the user experience for the Internet shoppers.
When restructuring a database like this — one whose content is in great part composed of binary images and extraordinarily long strings of text — it’s a best practice to separate the text and image data from the tabular data. Store the text and image data in one set of tables, and then use relationships to link each image or long text string to its associated tabular record(s).
You'll also want to separate the system (catalog) tables from the image and tabular data. You will want to place each one of these three types of tables in its own file group or table space.
Then, you will want to locate each file group on its own spindle. Using these simple principles of physical design, you'll significantly enhance database performance.
Third on the checklist is indexing for performance. Whenever you have a one-to-many relationship between two tables, you'll have a foreign key reference in the table on the many side of the relationship. In most database systems, the primary key is automatically indexed; that's not always the case with the foreign key. To ensure best performance on joins, make sure that every foreign key in the database is indexed. The next candidate for indexing is any column that will be used for sorting — any column that will consistently be used in an “order by” clause of a SQL query. You'll also want to index columns that will be used for restricting the returned data set, such as those that consistently appear in “where” clauses.
It's no easy task to determine, document and implement business requirements. It takes a lot of cross-functional collaboration, great attention to detail, and a validation of facts and assumptions in order to do it right. If you want to get it right the first time, define and adhere to a disciplined approach to gather and share the right information. Attention to detail while maintaining the big picture in your head is a must for good database design and getting it “right”.

Using the Open XML Format SDK 2.0

The Open XML Format SDK 2.0 simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages, so you can perform complex operations with just a few lines of code.

This documentation pertains to the second Community Technical Preview (CTP) of the Open XML Format SDK 2.0, released April 2009.
Using the Open XML API

Using the Open XML API is simple. In your project or application, simply add a reference to the DocumentFormat.OpenXml.dll. A link to the download containing the assembly can be found at the Open XML Formats Resource Center.

To add a reference to the Open XML API dynamic linked library file, perform the following steps.
To add a reference in a Microsoft Visual Studio 2008 project

   1.       In Solution Explorer, right-click References and then click Add Reference. If the References node is not visible, click Project and then click Show All Files.
   2.       In the Add Reference dialog box, click .NET.
   3.       Scroll to the DocumentFormat.OpenXml option, highlight it, and then click OK.
   4.       The filename is displayed in the Solution Explorer.