Data Management

Web services B2B implementation demonstration: Database design

In the third article of this series, Kevin Koch implements the necessary schema and scripts for SQL Server 2000, and the appropriate data tier classes for database connectivity and stored procedure invocations.

In part two of this series, I created the ASP.NET shell components and applications for both John's and Tom's applications. I defined a logging mechanism and established an exception tracing pattern for each component. At this point I'm ready to begin designing the database and writing stored procedures and database access code which conform to the requirements.

Web services B2B implementation demonstration
Welcome to the third article in a 10-part series exploring in detail the implementation of a B2B Web service. The series takes you through the development of a complete B2B Web services application. During the course of the series we encourage members to comment on the progress of the application. Do you have a better implementation? Do you have a question about our B2B solution? Start an article discussion and let us hear from you; we will do our best to address the issue. Want to catch up on articles in the series? Check out the first two articles:

Database design
I prefer to always begin implementation with database modeling. This is considered a bottom up approach. Others prefer to build user interfaces and work their way down to the database level known as the top down approach. I find using a top down approach creates code that is too specific and often not reusable. By abstracting yourself slightly from the requirements and creating entity relationship diagrams first, you can create a more flexible and reusable data tier component.

The key components in database design are entities, relationships, and normalization. Use the following procedure to create your databases and you should end up with a fairly accurate design every time:
  1. Identify your entities. You do this by examining customer requirements and identifying what needs to be stored and who owns that data. Usually the entities are the nouns in the sentence structure of the requirements documentation.
  2. Determine the data structure. For each entity you need to decide what must be stored to describe that entity. This data becomes fields in your database tables. Often customers have no idea how long text fields should be, or how many digits numerical input should be, so you must rely on your best judgment to create appropriate fields.
  3. Append System fields. Nearly every table requires fields that are not identifiable through requirements. System fields are things such as creation date time stamps, ID Primary key fields, last update date, etc.
  4. Create relationships. This is where you examine your entities and create relationships between them. For example, an order must be tied to a certain customer; therefore your order entity must have a foreign key to the customer table. The two most common types of relationships are aggregate composition (one-to-many: 1..n) and many-to-many: (n..n). Many-to-many relationships must be broken out into a separate support table consisting of only two primary key fields.
  5. Normalize your entities. This is where you take a final examination of your database model and optimize it by pushing redundant data out into its own tables. Normalization can reach an extreme, causing a degradation of performance due to too many table joins, so be careful what level of normalization you try to achieve.

Applying the knowledge
I'm now ready to implement the database models for both John's and Tom's applications. I'll use Visio for Enterprise Architects to create the database diagrams, which will also allow me to seamlessly generate the databases without writing any scripts. If you don't have Visio, I'll also provide the database generation scripts for both applications so you can manually create the databases.

By re-examining the requirements for each application identified in part one, you can identify the following entities:

John's App:
  • Client: This table stores clients that are capable of placing orders with John.
  • Order: This stores the details about placed orders.
  • Book: This stores Johns inventory of books.
  • Availability: This support table stores a list of availabilities. This table was added by normalizing the Book table and removing the redundant availability text field and replacing it with an optimized foreign key integer field to another table.
  • Order_Book_Rel: This is a many-to-many relationship table between books and orders. This is required because many books can be tied to many orders. By simply adding book IDs and order IDs to this table, you remove all redundancy required to tie orders to books.

Tom's App:
  • Order: This is used as a singular entity to map an entire order to a customer. The details of the order are stored in John's application.
  • Customer: This stores a list of customers who can log into Tom's application and place orders.

By adding some basic data fields to each of the tables above, I have the completed database structure.

Figure A diagrams John's database.

Figure A
John's database

Figure B diagrams Tom's database.

Figure B
Tom's database

SQL Server 2000
Before generating the databases, you must configure the instance of SQL Server 2000. You may use any version you wish, but for the purpose of this article, I'll describe how to set up the MSDE version which comes bundled with most versions of Visual Studio .NET (VS.NET).

Assuming your VS.NET is installed to the default directories, the setup application for MSDE can be found at this location: C:\Program Files\Microsoft Visual Studio .NET\Setup\MSDE. Simply run Setup.exe and follow the wizard instructions to complete the installation.

For working with your SQL server instance, I prefer to use the enterprise manager client utility, but you may also access your SQL server through VS.NET by clicking the Server Explorer node on the left hand tree.

You need to perform some modifications to the SQL server. Inside the enterprise manager, register your MSDE local SQL server by right-clicking the SQL Server Group node and selecting New SQL Server Registration. You should see your local machine name in the list of available servers. Once you're connected to your local SQL instance, right-click your local SQL server and select Properties. Click the Security tab and ensure that SQL Server and Windows is selected as the authentication type. This will allow you easier access to your databases through the default sa SQL server account.

With the instance of SQL Server ready to go you can now create your databases. Download John's and Tom's Visio file�. From within Visio click the Database menu in each and select Generate. Click the Generate new database checkbox and follow the wizard instructions. You'll need to set up a data source for both John's and Tom's database, which will allow you to connect directly and perform updates through the Visio model. After completing the wizard you should be able to see each database under the databases node of your server explorer.

If you don't have Visio, you can download the creation scripts for John (in Listing A) and Tom (in Listing B). Simply run these scripts in your SQL Query Analyzer or via command prompt tools to generate the databases.

Data access classes
Once the databases are in place, you can begin implementing your data tier classes in both applications. The actual linkage between your data entity classes and the database should always be provided through a generic data access class, which handles all of the common database functionality you'll need in an application. I'm going to use Microsoft's Data Access Application Block which is a reusable set of classes used to perform database connectivity in an optimized manner.

I'm going to take the basic fundamentals required for the application and strip it down to bare essentials. For your applications, you need only to execute stored procedures and create SQL parameter variables; the rest you can strip out. In both DbTier components for John and Tom, add a new class to this component called DbAccess.vb. Copy the code from Listing C into both new classes.

This data access class is a reusable component that can be applied to nearly any application which requires basic database connectivity. The features of this class are as follows:
  • Connect to a database. By using a setting in your Web.config file, you can generate a connection to any database. Add the following key to your appSettings node in your root Web.config file:
    <add key="DatabaseConnString" value="server=yourmachinename\VSDOTNET; database=(either "JohnDb" or "TomDb"); uid=sa; pwd=;" />
  • Execute a stored procedure that returns a database. This is used for stored procedures which execute SELECT statements.
  • Execute a stored procedure that has no return value. This is normally used for stored procedures that update or delete data and return nothing back to the user.
  • Create SQL parameters. For stored procedures that require input, you can call generic functions to create input parameters.
  • Cleanup. The DbAccess class implements the IDisposable interface, ensuring that when you close your connection, that the connection is immediately returned to the connection pool.

Each database requires a set of stored procedures which represent your use case functionality. The stored procedures basically represent everything that your applications are capable of doing. By putting SQL syntax into stored procedure, you save a lot of headaches by not having to write SQL queries in code. Another benefit is that you can change the behavior of your SQL queries at run time without recompiling and redeploying the application.

Open the SQL Query Analyzer and execute the following SQL scripts on the appropriate database, which will create the stored procedures required for both applications. Make sure you run John's script against John's database, and Tom's script against Tom's database.

Finally, there are three tables which require seeding to default values. They simply insert some default values into certain tables so that you have data to work with in your application:
  • Listing F generates the default book availability types. Each book has an associated availability.
  • Listing G creates a default list of books that you can use for placing orders.
  • Listing H creates an entry for Tom; because Tom is a client of John, he must be present in this table otherwise Tom wouldn't be able to place orders in John's application

Next, you need to create your DbTier classes for each entity. Based on the requirements, there are certain operations that you've determined to be performed on each entity. Add the following classes to the appropriate application's DbTier component:

Each database table you've created has an associated data tier class. This is the simplest way to model your data tier and creates a much more organized component than simply creating several methods in no particular pattern which execute the stored procedures.

Examine the reusable pattern which is present in each data tier class. You use static constants to represent nearly every attribute in your database. By creating a separate class for each table, you can bind variables to stored procedure names, stored procedure parameters, field names, etc.

Each data tier class has the following header format:
  • Stored procedure names: Here you define the names of each stored procedure which is executable by the class. Should a stored procedure name ever change, you need only change it in one place
  • Stored procedure parameters: For each parameter, you define the name and the size. Again by using constants, if any of these values change, you need only change it in one place; for example, you don't have to sift through every method if a field length changes from 10-15 characters.
  • Field names: The public constants give other components access to the field names of the table this class represents. You can use these constants to pull data out of a dataset by using names instead of index references.

In each method, you simply reference these constants to execute your stored procedures and pass it values. The simplicity and reusability of this design pattern is incredibly advantageous in creating developer-friendly code. This format is used for every data tier class and can also be applied to nearly any average sized application.

Next in the series
In the third installment of Kevin Koch's Web services B2B implementation demonstration, he will cover the design and security principle for John's Web service layer, identify the methods required from the use cases and requirements, implement the SOAP Header authentication context, and implement the Web methods required and tie it into the Data tier component.

Another benefit is that there's no need to manage foreign key relationships. If a class requires a foreign key ID to another table, simply reference the foreign key's data tier class and access the ID required. For example, to use OrderId from the Customer data tier class, use OrderDb.PARAM_ORDER_ID_NAME. The data tier classes don't need to be aware of their relationships to other entities.

Editor's Picks