Frank's Ten Minute Lectures on Client/Server Database Architecture

Client/Server is an approach to storing and processing data characterized by the division of data processing labor between two asynchronous pieces of software. Generally these pieces of software communicate with each other over a network channel and typically reside on separate computers.

It is important that from the beginning you see the "Server" as a piece of software and not as a computer. Similarly, a "Client" is actually a piece of software. You should not automatically associate this term with a workstation.

OMNIS 7 applications, ad hoc query tools like Brio's Dataprism, ISQL applications like that available on UNIX are examples of client applications. The Sybase dataserver product can furnish data to any or all of these applications simultaneously.

The dataserver software is home to one or more databases. Each database is in turn made up of tables. Each table is composed of columns and rows, much like you might find in a spreadsheet devoid of formulas. There are other objects in databases that are used to relate, organize and regulate the data in tables. Databases that are organized into tables with defined relationships are known as "Relational Databases." Sybase is an example of a relational database.

When a request for specific items of data is received by the server from the client, the server first interprets the request and forms a query plan. Then the "database engine" executes the query and typically queues the resulting data for return to the client across the network.

There are three different motivations affecting how someone might answer this question. The Systems perspective is that one needs to provide service to the widest possible variety and number of users given the limited machine resources at one's disposal. The Systems emphasis therefore is on application efficiency and tuning. Vendors generally are motivated by profit. Thus, they try to provide a product that performs to spec with the least amount of effort or expense. They generally are not concerned about another author's applications accessing the server, they have very little motivation to optimize their code or methods. In addition, vendors tend to favor general rather than custom solutions to problems even when custom solutions may be more efficient. They do this in the hope that the general solution may be resold with little if any additional overhead. Managers and applications programmers are generally motivated by user demands for productivity and tend to be conscious of development deadlines. Many times this emphasis on timely product leads to shortcuts and sacrifices of quality or efficiency.

First, let me emphasize that there is no such thing as a program that "runs on the server." Application programs are client programs. It is not wise from a systems perspective to attempt to run client programs on the same machine with the server. There are several quite simple reasons for this. First, periods of intense client CPU activity are likely to coincide with periods of intense server CPU activity. The result will be seen by load averages that exceed "1" indicating that some tasks are having to wait for the CPU to become available. Second, periods of intense disk activity are likely to correspond. As the client receives the data it must either store the data on disk for further processing or store the data in RAM. As the client application competes with the server for RAM, virtual memory paging will likely increase causing increased disk accesses. Third, periods of intense network activity will coincide. Since the local application uses the same network port mechanisms to connect to the server as remote applications there is a tendency for the local applications to hog the server's network i/o time. This leads to unsatisfactory remote application performance.

All data integrity constraints, business rules, and security must be implemented on the server as part of the database design and internal coding. This is necessary because one cannot guarantee in a client/server environment what kind of client is in use. The server is indifferent to what application is connecting to it. As long as the user gives a valid login and password which is authorized to access a database, they are allowed to do so. So, although you might intend to only allow a certain OMNIS 7 application to update your database, there is nothing to stop someone with a valid userid and password from logging on through ISQL or Dataprism and modifying data outside your intended application. Thus, you must ensure that however the modifications are done, the integrity of the database will be maintained.


As a simple example, let's say that you have two tables participating in a parent-child relationship. For example one table has "Students" in it and another has "Student Course Enrollments." The Students table might use the UKId number as its primary key. Then, UKId would be a foreign key within the Student Course Enrollments table. You would not want to allow someone to enter a Student Course Enrollment row unless there was a corresponding UKId number row in the Students table. If you choose to enforce this within your data-entry application and someone bypasses your application by using a different tool to update the data, there is a good chance they could violate this rule. On the other hand, by using either a "declarative" constraint or a trigger you could ensure that no row could be added to Student Course Enrollment table unless there was a corresponding Students row. These triggers or constraints would be checked every time an item of data changed in either table which might affect the relationship. Now, if any application attempted to violate the constraint, the database server software would reject the request and return an error code and message

I once had a programmer ask me:

I am starting a new project for department X and would appreciate your input before I get started. What are the major performance issues for having one large SQL table with all of your fields vs. breaking up the table into several small ones? All 70 or so fields I will need are all related to one entity, the person filing the claim. Would Omnis work more slowly with Sybase depending on how my database was arranged?

Almost certainly the data for this project should be kept in one database, although it may be necessary to extract and/or reference data from other existing databases (like employee or ukaccess). Probably that database will have more than one table. There are very few databases that consist of a single table.

From the sound of it, the project at the very least involves two tables because the question says that the "claims" relate to "people." There are two nouns in that, so that's at least two tables. To be sure, you can ask the question: Is it possible that there might be two claims from the same person? Or, since this is an extension of the existing employee data might there be some advantage to simply linking the "claims" table to the "person" table in the employee database?

In order to figure out exactly how many tables are needed you should go through a process called Normalization. This consists of repeatedly applying a series of rules against the data eventually distilling it into what is known as "fourth normal" form. These rules are available from many different texts. From a practical standpoint, you could read pages 2-18 through 2-22 of the OMNIS 7 developer's Guide (The thick book). . .that's a capsule summary and is not complete, but it is short. There's also a good summary in Joe Celko's book SQL for Smarties which I recommend. If you have access to the web, look at http://www.state.sd.us/people/colink/datanorm.htm where you'll find the five rules of data normalization.

Part of the DBA's job is to help with the layout of tables. So, if you're looking for help, see your favorite DBA.

Just because a table has 70 columns (fields) in it doesn't mean that you have to retrieve all 70 each time you do a query. Just as "selection" is used to limit which rows you retrieve, "projection" is used to limit which columns you retrieve. Especially in OMNIS programs, you should never do a "select *"; instead, define file formats that correspond to the rows you want to retrieve from a table or the join of multiple tables and use that file format in the projection portion of the select:

"select selectnames(fileformat) from sybasetable where ...."

The names in the file-format must exactly correspond to the Sybase names including case. Therefore you must have chosen the correct OMNIS preferences so that case sensitivity and naming conventions correspond to those of Sybase.

The first tendency of newbie database programmers is to index everything. This is neither practical, nor wise. In Joe Celko's book, SQL for Smarties he gives at least one example of when the presence of an index actually slows down a query (See section 28.4). Here are some basic guidelines on Sybase indexing.

First, you should understand that declaring something the "primary key" of a table is purely a comment as far as the integrity of the table is concerned. Enforcing the rule that the primary key must be unique is done with an Unique Index. So, each table should have a primary key which is unique, which means that each table should have a unique index declared on the column(s) that form the primary key.

Next, based on the formal relationships that exist between the tables you should be able to identify the foreign keys within each table. Since most joins will involve these foreign keys, each of these should be indexed. Probably, these indexes should not be unique, unless the foreign key is also a candidate key.

In the first iteration of a new database the indexes described above are sufficient to get started. Additional indexes may need to be added as frequent query paths are described for the data. Thus, even if the client's name is not a foreign or primary key, if it used frequently by the users to locate the client's records it should probably be indexed.

You should specifically avoid indexing fields with many repeating values. Thus a Boolean field filled with either a "Y" or an "N" would not be a good candidate for indexing.

A "Clustered" index actually determines the physical order of the records in the table. Thus, only one index can be declared a "clustered" index in a table. Another beginner's mistake is to cluster every table based on the index for the primary key. Instead, you should choose which index to cluster based on which records are most likely to be retrieved together from the table. For example, if the system described in the earlier question is to be used mainly by departmental users it might be beneficial to cluster on the index "department number" within the claims table. This would be in anticipation of the tendency for a department user to want to "see" all the claims from their department at once. Clustering in this way would tend to group all the claims for a department close together on the disk. Thus, the rows could be retrieved with less disk head movement, implying less delay. This would also have an effect on the number of "read locks" obtained on the table during the query. Locks are a system-wide resource in Sybase. Since Sybase uses a page-locking scheme, by clustering the rows within the same page fewer locks must be obtained during the "select". Running out of locks could indicate that the data might be more efficiently clustered on a different index. (Other things could also cause this condition).

Creating indexes within a Sybase database does not force the database engine to use them. There is no syntax in SQL that even suggests which indexes exist or are to be used in a query. (Actually there is, but its use is discouraged). These decisions are completely under the Query Optimizer's control. You can determine whether a particular index was used by a particular query by using the Sybase statement: Set showplan on before executing your query. Two other helpful query statistics commands you should know about are Set statistics time on and Set statistics io on. By running a few simple tests with your most used queries you can sometimes gain enough data so you can rewrite them to be much more efficient, or choose indexes to greatly increase their speed.

Tables distinguish Relational databases from other data structures. Tables with columns containing common values are said to be related. If the columns of the relationship happen to be composed of the primary key in one table and a foreign key in the second, then a formal parent-child relationship exists. All formal relationships in a normalized database will be one-to-many relationships (with one-to-one and zero-or-one-to-many being specific cases of one-to-many). One simplified way of representing these relationships in a diagram uses arrows:

These relationships suggest that certain rules must be followed when adding, deleting or modifying data. For example, you should not be able to add a row to a child table for which there is no corresponding parent table row in the case of the One-to-Many or One-to-One relationships.

Enforcing these "referential integrity" rules in a database can be done in two different ways, using the declarative method or by coding triggers. The declarative method follows the ANSI standard for SQL and allows for a limited set of rules to be enforced. For example when defining the claims table as per the now extended example:

ClaimCode char(1) not null)

causes any insert into the newly formed claims table to fail if there is not an already existing UKId in the employee table which matches that column in the inserted row. This particular example is a column level constraint. There are also table-level constraints that relate the values in multiple columns to a parent table.

By using triggers and rules one can achieve a more flexible version of the desired effect. A trigger is a piece of Transact SQL code, similar in principle to a stored procedure, but it executes automatically when a certain event happens. For example, a trigger can be set to occur on inserts, updates or deletes to a table. Through judicious use of rollbacks within a trigger you can prevent the insert, update or delete from happening if it would cause violation of one of the integrity rules. Here is a very simple example that duplicates the insert behavior associated with the declarative version given above:

for insert

as

if (select count(*) from employee, inserted

begin

print "You cannot enter a claim for someone not in the employee table."

rollback transaction

end

Understand that in order to fulfill the requirements for referential integrity using this method there would also need to be an update trigger on claims and a delete trigger on the employee table. To facilitate the writing of triggers there are two special tables available to them: inserted and deleted. For a discussion of how these are used read chapter 14 of the Sybase manual Transact SQL User's Guide.

Because of the greater flexibility of triggers, they are the preferred method for enforcing referential integrity in Sybase databases. One must be careful however, to keep the SQL short and efficient within triggers to avoid a performance impact on the server.

Although tools like dbArtisan allow the creation of tables, views, triggers and other database objects in a rich interactive GUI environment there are advantages to maintaining the source code used to create the production database objects in a central repository. In an emergency-restore situation it is possible, if you have source for your database objects handy to re-create the database structure with only minimal physical access. In a development environment, maintaining the code in a source code control system allows for free experimentation. If something does not work out, you can restore the affected database objects to a previous version by simply retrieving the correct version from the SCCS and running the script. This has three main advantages from the viewpoint of programmer productivity. First, only one copy of the source code need be maintained. Second, the programmer does not have to worry about having destroyed the previous version of the object's source in case a latent bug is discovered weeks or even years later. And third, since each programmer must check out an editable version of the program before working on it, the programmer doesn't have to worry about someone else's modifications overwriting his or hers.

There are five basic commands associated with the UNIX version of the SCCS.

The OMNIS 7 Version Control System provides source-code control for OMNIS programs. It stores the source in a Sybase database. Like the UNIX SCCS it provides concurrency control to prevent cooperating developers from modifying the same "OMNIS formats" at the same time. It also provides versioning so that it is possible to reproduce previous versions of an application. Because the OMNIS VCS works at the "format" level it provides the developer with a way to construct or reconstruct a library using components from other projects. Common components can be store once and reused. In this way, new functionality can be added to many applications at once by simply modifying the common formats.

It has been our experience within the Systems group that the OMNIS VCS is less stable than the UNIX SCCS. However, there are few if any alternatives to the VCS since the UNIX SCCS will not handle binary files. Some helpful hints which might make your use of the VCS less troublesome:

One general principle used by many modern development teams is to prefix names with a set of characters denoting the type of object. In keeping with this we have adopted these conventions:

View names will not be prefixed since they are the primary database objects that an ad hoc user might interact with.

Table names will be prefixed with an uppercase Z. For example, ZPerson, ZLogin, ZCourse.

User defined types will be prefixed by a lowercase u. For example, uBoolean, uLoginId, uBoxCd.

Rules will be prefixed with a lowercase r. For example, the rule which might govern what characters can be used by a variable defined to be uBoolean might be called rBoolean. Similarly, other examples could be rLoginId, rBoxCd, and so on.

Triggers will begin with a two-letter prefix indicating not only that the object is a trigger, but what kind of trigger. Thus, an Insert trigger would begin with "ti", an update trigger with "tu" and a delete trigger with "td". Combination triggers would combine the letters in the order "iud" for "insert, update and delete. For example, a trigger that fires for both inserts and updates on the ZPerson table would be called tiuPerson. A trigger that fires when something is deleted from the ZLogin table would be called tdLogin and so on.

An index should begin with a lower case ind to avoid confusion with the integer type for which we have assigned the lowercase i. Thus an index on the column UKId within a table would be named indUKId.

System stored procedures are named by Sybase using the prefix sp_. We've tried to avoid using underscores in our UK naming convention. UK written stored procedures begin with a lowercase p. For example, pNewLogin, pModSvrLogin, or pGenId. The name of a stored procedure should indicate its function.

Note that throughout the examples, capitalization is used to separate words within the name since we have decided to avoid underscores. Sybase is case sensitive. Thus the following examples: pNewTGenRich which would be a procedure to possibly create new rows in the "T" table while generating riches for the caller. Versus pNewtGenrich which might be a procedure to cut taxes and generate riches for the speaker of the house. (Programmers never could spell or is that sp_ell?).

We decided that it would be best if table names were not plural. Thus, call your table ZPerson, not ZPeople, or ZLogin not ZLogins.

Database names we decided should be all lowercase. Server names we decided should be all upper case. Of course, since these items are case sensitive and the server name is mapped to the server's ip address at the client, it is important that these conventions be known and followed by anyone doing installations of the Sybase workstation software.

Coding for OMNIS 7 involves many naming decisions for objects within the interpreted programming language, external GUI, database, or sometimes even operating system. I wish I could say that these naming conventions were covered in a comprehensive document that we produced as part of our SEGUE workshop. But, unfortunately, the two documents we produced as a result of that workshop are not complete and worse, contain generic material that does not reflect our standards or practice.

At the same time, the basic principles that I mentioned in the previous section also apply to OMNIS programming: Generally, we agreed that "type" information would be conveyed in the object name. The names need to be case sensitive. Each major word in the name should be capitalized. Underscores should be avoided. Lower-case type prefixes are used to help identify objects of common types. In addition to the concept of type, OMNIS 7 has the concept of variable scope. Scope determines the "visibility" of a variable. Local OMNIS variables are "visible" only within the procedure in which they are first defined. Format variables are "visible" within any procedure of a "Format." Examples of Formats are "Window Formats", "File Formats", "Menu Formats", "Report Formats" or "System Formats." Library variables are "visible" throughout all the formats in a library. Each library is represented by a source file stored on disk. Several libraries may be active in an OMNIS instance at the same time. Global variables are "visible" to all libraries within an instance of OMNIS. The naming convention for Global variables is fixed within the definition of OMNIS.

In addition to the naming conventions for variables we've also established naming conventions for OMNIS Formats. Window formats for example all begin with a "W"; Menu formats begin with "M"; Reports with an "R". File formats we agreed to name following the Sybase convention for table names since they roughly correspond. Field names within the file formats also correspond with Sybase Column names. Thus, they must be case sensitive and when fully qualified should include the "file format" name to which they belong. For example, if you are referring to the "Dept" field within the "Account" file format, the fully qualified name would be "Account.Dept". Thus , it could be distinguished from the "Dept" field in the "Dept" file format: "Dept.Dept" and so forth.

The table below summarizes the prefixes for distinguishing OMNIS scope and type.

Writing the underlying logic for reports and batch processing requests in SQL provides for the most flexibility and portability between client platforms. SQL is weak when it comes to formatting since many of those decisions are under the control of the client program which executes the SQL. Even so, many common reporting tasks can be accomplished with nothing more than SQL executed using the ISQL utility or the WHISTLE program.

A few of the common command line parameters for the UNIX ISQL utility that you'll find useful:

SQR is a product now owned and supported by Sybase partner, MITI. SQR is an extension to SQL which allows formatting information to be interspersed in standard SQL. An SQR program is executed via a runtime client which passes standard SQL to the Sybase dataserver engine and does classical reporting on the results. As with any reporting language, SQR requires time to master.

Excel is a powerful computational tool with extensive formatting and good graphics capabilities. In Microsoft Office it is packaged with Microsoft Query which allows for ad hoc queries through an ODBC interface. It is also possible to use Brio Query to export data to an Excel spreadsheet for further manipulation. This avoids having to configure ODBC in addition to the native Sybase drivers on the PC. One can also use a primitive query tool like isql or WHISTLE to extract data from Sybase using raw SQL, then import the results into Excel. Excel includes tools and functions to convert the text files generated by these primitive query tools into appropriate data types for reporting and manipulation.

Microsoft Access also comes with several reporting tools that can be used in conjunction with Sybase. There are two separate approaches to this. One approach is to create tables in Access that actually represent remote Sybase database objects. For reports that only occasionally make simple references to Sybase tables this may be adequate. Frequently accessed data can be extracted from Sybase and stored in a local Access table. This can be done by first writing a query which is executed by ISQL, WHISTLE, or MS Query, then importing the returned data as a text file. Like Excel, Access has tools that make the importing of text data into a table easy. It is also possible to bring Visual Basic for applications to bear on any special import problems you might encounter.

OMNIS 7 has a flexible reporting facility built into its development environment which allows reports to be incorporated into applications. Application users can then access these reports by clicking on objects which run the reports, prompt for parameters etc.