Application design is more than just writing efficient database requests in application programs. Of course, application design includes database concerns such as interfacing SQL with traditional programming languages and the type of SQL to use. But every aspect of the way the program is coded will affect the usability and effectiveness of the application. Furthermore, each application program must be designed to ensure the integrity of the data it modifies. And, of course, performance must be treated as a design issue.
Many organizations erroneously assume that the DBA should not be involved in application design. The common line of thought goes something like this: “DBAs manage the database software and programmers manage applications so I don’t really want DBAs mucking around with programs.” Maybe this would work in some perfect world where all programmers understood the DBMS well enough to program efficient database access – but not in this world.
The DBA must promote the concept of designing applications with an understanding of the database at the forefront. It is unacceptable to allow programmers to design and code applications without considering how the programs will perform as they interact with databases. The uninformed will approach database application development with no proactive performance engineering – the assumption being that any performance problems can be resolved after development by the DBA. But it may be impossible to tune an improperly designed application program without rewriting it. So why not write it correctly the first time?
Designing a proper database application system is a complex and time-consuming task. The choices made during application design will impact the usefulness of the final, delivered application. Indeed, an improperly designed and coded application may need to be redesigned and re-coded from scratch if it is inefficient, ineffective, or not easy to use. In order to properly design an application that relies on databases for persistent data storage the system designer at a minimum will need to understand the following issues:
- How data is stored in a relational database
- How to code SQL statements to access and modify data in the database
- How SQL differs from traditional programming languages
- How to embed SQL statements into a host programming language
- How to optimize database access by changing SQL and indexes
- Programming methods to avoid potential database processing problems
In general, the developer must match the application development languages and tools to the physical database design and the functionality of the DBMS being used. The first thing to be mastered, though, must be a sound understanding of SQL, the lingua franca of the relational database world. SQL is different than most high-level programming languages.
SQL is designed such that programmers specify what data is needed – not how to retrieve it. That is, SQL is coded without embedded data-navigational instructions. The DBMS analyzes each SQL statement and formulates data-navigational instructions “behind the scenes.” The DBMS understands the state of the data it stores, and so it can produce efficient and dynamic access paths to the data. The result is that SQL, used properly, provides a quicker application development and prototyping environment than is available with corresponding high-level languages. Furthermore, the DBMS can change access paths for SQL queries as the data characteristics and access patterns change, all without requiring the actual SQL to be changed in any way.
Most programmers are accustomed to hard-wiring data navigation instructions into their programs. Making the switch to SQL can be troublesome without some training. At any rate, programmers will need to be trained in these high-level differences between non-database and database programming techniques. This job falls to the DBA. Of course, there are many more details at a lower level that the database programmer needs to know, such as SQL syntax, debugging and testing methods, optimization techniques, and program preparation procedures (e.g. compilation, bind, etc.).
Additionally, application programs require an interface for issuing SQL to access or modify data. The interface is used to embed SQL statements in a host programming language (such as COBOL, Java, C, or Visual Basic). Standard interfaces enable application programs to access databases using SQL. There are several popular standard interfaces or APIs (Application Programming Interfaces), for database programming including ODBC, JDBC, and SQLJ.
Of course, the DBA will need to clearly describe the database interfaces to that are available for the programming languages in use in their organization. And the DBA must provide proper guidance and training such that the correct interface is used. Of course, that means that the DBA must have a solid background in — and knowledge of — the various programming languages being used within their shop and the ways in which they interface with the DBMS.
Performance can vary greatly for different interfaces and versions due to issues such as dynamic versus static SQL support, optimization of the interface, and the use of proper compilation and binding parameters and techniques.
Of course, these are just a few of the issues where the DBA can help to improve application design. Others include publishing up-to-date SQL coding techniques and tips, ensuring proper transaction coding including database commit frequency, interaction with TP systems and middleware, and coding to minimize locking problems. Application design and development is the core responsibility of systems analysts and application programmers, but the DBA must be involved in the process when programs are being written to access databases.