Sunday, February 24, 2008

Upcoming Topics

This is an extra entry that I will update from time to time that lists planned upcoming essay topics.

Last Revised: May 4, 2008. Removed some topics that have since been covered. No additional topics were listed in this update.

ACID and Transactions

A huge part of effective database use is knowing what the ACID concept is and how to effectively use transactions. There will be at least one essay on these topics sometime in the future.

Additional Design Patterns

Some of the patterns coming up in the future are:

  • The ranged primary key, usually thought of in terms of dates but also useful for quantity price breaks, among other things.
  • How a foreign key into a ranged primary key can work.
  • The DELETE CASCADE pattern
  • Various ways to treat summary and detail table sets, like an inventory table and all of the various child tables that track things in and out of it.
  • Audit tables

Server-Side Code

Databases become very powerful when you tap into the abilities of triggers and stored procedures. We could easily have 4 or 5 essays on the various amazingly nifty things you can do with these technologies.

A Vocabulary Essay

Reasonable discussion of database concepts is often frustrated by the fact that there are three different groups of people using different terms for some of the same ideas: the relational crowd, the SQL crowd, and the coding crowd. What makes things worse for the new database programmer is that these three groups have different and often conflicting assumptions and goals, and these conflicts are not always stated outright when they speak. At some point I would like to do an essay explaining the three groups, the terminology they use, and what their assumptions and goals are.

Physical Reality and Performance

I have always found that great performance begins with table design and query design. After that, there are some basics that you need to know about indexes and database tuning, but after that performance tends to come down to knowing your application tools. One programming language may work best by fetching down the entire results of a query before processing it, while another works better by fetching and processing row-by-row.

At some point there will be at least one essay on the details of performance.

Handling Data Imports

Many database applications must accept imported data from other systems, often in large quantities. The source and destination tables often have different structures, and major performance issues can really slow down a project if you do not know the tricks for handling large quantities of data.

I have two or three essays in mind on the issue of importing large blocks of data into databases.

Security

It is probably safe to say that in 2008 most programmers using databases have no idea that a database is even capable of performing security. It is probably a complete surprise to most young programmers that proper use of server-side security renders your application immune to SQL injection!

In future articles I will present the basic and advanced concepts of server-side security.

Philosophy of Life

I have a few essays in mind which are more about the ideas that guide development. One of them has to do with where you put application logic, and another has to do with the analysis and table design process. There will be others as well over the coming months.

5 comments:

Greg said...

Just wondering if you had any thoughts on recent claims that moving to a new, more column focused database is a better way to store and query on data.

http://www.informationweek.com/news/showArticle.jhtml?articleID=206801203

KenDowns said...

The article you mention stresses the use of these databases for data warehousing applications. From my own experience I would expect they would be very useful for marketing databases as well, since they basically turn your database into a bunch of indexes.

As far as my own blog goes, there is an implied focus on transactional activities, which will not be affected by the column-oriented approach.

Anonymous said...

Server side security?!?
I wonder what you mean. I have no clue as to what this may be. I really liked this series of articles.

KenDowns said...

anonymous: if you want a preview, check out "GRANT" and "REVOKE" in the docs for either MS SQL Server or Postgres. The basic idea is that different users or groups get different read/write permissions table-by-table. Using different combinations you vastly simplify your app code and have much stronger security.

Anonymous said...

Would be very interested in your views on accessing databases from programming languages (esp Java) - ie ODBC/JDBC and tips/pitfalls when using it, including accessing metadata (ie tables, fields, indexes etc) and related inventions like JSQL, SQL-J.