Information Stores and the CML

Information resides in many diverse information stores. The data store can be mandated by the type of data; often it is simply the information store where the data happens to reside. The Corporate Media Library (CML) application does not require integration of legacy databases. Therefore the developers do not have to cope with where data already resides and instead can focus on choosing the best-fit information store for transactional library information. The CML application requires access to Fitch & Mather's Microsoft® Exchange Server directory, where employee information is kept.

When choosing appropriate data stores for the CML application, the development team considered:

• Characteristics of CML data. The CML application needs to store information about library titles, the borrowers of library materials, and miscellaneous data associated with titles and borrowers. The developers decided to use system-defined data types: Integer (bit and tinyint) for Boolean values and sequencing requests, Money for the prices of library materials, Datetime for any date-related elements, and Characterstring (varchar and text) for all text-type fields. No user-defined data types are used.
• Characteristics of CML database activities. Searching the library database and displaying the content of the library will certainly be the most frequent CML application activity. It is expected that a user will make three or four searches during each visit to the library Web site. The relational engine of the data store must support efficient searches. To facilitate searches for library materials a user must be able to submit textual searches on tables in the information store. This will require implementing full-text search.

  The second most frequent database activity will be handling requests for library titles. Users will submit requests to check out library titles based on the searches they make. A record is inserted in the data store for every checkout request a user submits and the record is deleted when the administrator fills the request. These are very fast and short transactions.

  The third database transaction category supports maintaining information on library materials — inserting, updating, and deleting records.

• Characteristics of CML data access. The database will be accessed from the CML application ASP pages and components using RDS and ADO respectively.
• Characteristics of the deployment environment. The CML is an intranet application and there is a real need to access the data stores from ASP pages and from components residing in the business-services tier.
• Data integrity. Maintaining integrity of the library data is of high importance and requires the support of referential integrity, FOREIGN KEY constraints, and database triggers. Transactions involving multiple CML database tables will not be a common occurrence, though CML components in the Microsoft Transaction Server package CML.pak are enabled for transactions.
• Size of database. The CML is expected to have no more than 15,000 to 20,000 records in the largest tables, and the overall size of the database is projected to be between 8 and 20 MB.
• Locking. The number of concurrent users accessing the CML data stores is expected to be one percent of the F & M employees or about 45 persons, and the CML needs only the locking that SQL Server implements automatically. If the number of concurrent users greatly increases, locking strategies should be revisited.