High volumes of users or clients requiring concurrent connectivity characterize the Online Transaction Processing environment. Each client can conceivably initiate several data manipulation (DML) statements that SQL Server accepts, logs, processes and executes. As a result, a predefined data model must exist prior to physical model creation. This model, crafted by database developers and administrators should directly support the multitude of small insert, delete and update statements indigenous to a typical OLTP application. Upon designing the logical data model, designers must account for user concurrency, atomicity, speed of information retrieval and the speed with which records can be updated or written.
Characteristics of an OLTP data model include a high degree of normalization. The normalization of data into relational tables optimizes the speed of transactions performed by the OLTP application.
Normalization of tables optimizes the efficiency of the transactions occurring in an OLTP environment. By using formal methods to separate data into multiple related tables, client activities in the database during production can be accommodated more efficiently.
An advanced degree of normalization will be conducive to the multitude of DML statements and reads in a transactional environment. This improvement in performance will increase transactional consistency and efficiency.
As the degree of normalization increases, the complexity of the data model invariably will increase. The amount of relationships and constraints which must be applied to the model to maintain referential integrity can become equally intensive as well as difficult to administer. Additionally, many complex joins between tables will begin to hinder performance. The determination of the degree of normalization should be comprised of a balance of normalization form methodology and performance considerations.
OLTP database design should attempt to closely follow the rules and forms surrounding data integrity. When designing the OLTP model, Entity Integrity, Domain Integrity and Referential Integrity should be employed. Through the use of check constraints, primary keys, column default values and relationship constraints, the model will actively preserve data integrity.
Because the data model maintains business rules about the integrity of the data, logic involving appropriate values for columns, relationships and data validation is no longer integrated into the client. This approach allows for the slimming of the client into a purely presentational device.
courtesy Microsoft SQL Server Books Online
Referential Integrity example
Procedural integrity also allows for a thin client by assisting in maintaining data integrity through the use of stored procedures, triggers and check constraints. Using these SQL Server programmatic methods to ensure business rules on the data model will allow for the client to exclude the programming required to handle these procedures.
SQL Server default and rule objects are not ANSI standard and should be considered as a mechanism for backward compatibility with SQL Server 6.5 only. Column defaults and check constrains should be used instead of these legacy database objects.
As an organization collects data, an inherent organizational need to analyze the transactional data and supply business users and analysts with this data will arise. However, because of the characteristics of an OLTP environment (multiple users concurrently inserting, modifying and deleting records), reporting becomes a resource intensive process.
Reporting in an OLTP environment will add additional traffic to the data model. In addition to the typical DML activities, the database now has to support the reading and selection of data for read purposes. Because of the degree of normalization present in many OLTP models, the multiple joins and tables hinder read performance and increase the resources required for report generation. Simultaneously, the read operations required by reports are obtaining locks on records, utilizing indexes and performing joins to gather required information. These operations will affect the speed and efficiency of transactional operations
Online Analytical Processing (OLAP)
reporting in the context of a data warehouse will effectively alleviate the strain placed on an OLTP schema by reporting requirements. OLAP or data warehouse solutions will also increase read efficiency for information consumers by gathering and transforming OLTP data into a star schema. The star schema is optimized for read efficiency and can be populated nightly.
The creation and design of tables (entities) and their fields (attributes) should be a process driven by meetings with the business user. The business support for the application will be vital to the correct design of tables and relationships in the OLTP data model. At each stage of logical modeling, the business user should be given the opportunity to verify and correct the relationships and table structures presented by the database developers.
Designing entities in SQL Server requires a database developer to have a full understanding of data types and the ramifications of their use. Specific data types can be used in a variety of situations. However, not all data types fit a design situation as readily as others.
Data Type Specifics
VARCHAR vs CHAR
A varchar is a variable length data type that holds character data. It differs from a char because a char value is a fixed length data type. In other words, a varchar (30) holds up to 30 characters. However, if a 10 character value is stored in this field, the space used in SQL Server is only that of 10 characters. A char(30) containing 10 characters requires SQL Server storage space of 30 characters.
Varchar should be used when the length of character data in a field is expected to vary. A char data type should be used when consistently expecting data to be the same length.
Nvarchar and Nchar data types support Unicode data (international character data) and require twice the space of their Non-Unicode counterparts. These data types should only be used when anticipating Unicode data.
INT vs DECIMAL (NUMERIC)
The int data type only accepts whole number numeric data. The decimal data type accepts numeric data with a definable scale and precision. The numeric data type is a synonym for the decimal data type and will not be supported in future releases of SQL Server.
Use the int data type where appropriate. Storage space requirements are smaller than that of a decimal. The int data type accepts values ranging from -2,147,483,648
to 2,147,483,647. However, the int data type would be inappropriate to use when attempting to house data such as a phone number (a phone number of 214-748-3650 or 2147483650 would never be accepted into an integer field because it exceeds the value that the int data type can hold. Use a char data type for phone number fields. In this manner, developers and coders can search for area codes and other patterns in the phone number field).
Other entity design issues include several considerations that database developers should take into account when designing the logical and physical mode. For example, field names should be logically descriptive and related to their business function and significance.
Field names should not include any characters except alphanumeric (preferably alpha) characters. Spaces, control characters, and other symbols all contribute to allowances that developers and coders must make to accommodate outlandish field names.
Additionally, field names should be concise and not overly verbose or lengthy. This alleviates the strain placed on developers and coders who must utilize these field names in code.