Articles tagged

09 June 2011
09 June 2011

A Tale of Identifiers

Identifiers aren't locators, and they aren't pointers or links either. They are a logical concept in a relational database, and, unlike the more traditional methods of accessing data, don't derive from the way that data gets stored. Identifiers uniquely identify members of the set, and it should be possible to validate and verify them. Celko somehow involves watches and taxi cabs to illustrate the point. … Read more
01 April 2011
01 April 2011

Improving Comparison Operators and Window Functions

It is dangerous to assume that your data is sound. SQL already has intrinsic ways to cope with missing, or unknown data in its comparison predicate operators, or Theta operators. Can SQL be more effective in the way it deals with data quality? Joe Celko describes how the SQL Standard could soon evolve to deal with data in ways that allow aggregation and windowing in cases where the data quality is less than perfect … Read more
04 January 2011
04 January 2011

BIT of a Problem

The BIT data type is an awkward fit for a SQL database. It doesn't have just two values, and it can do unexpected things in expressions. What is worse, it is a flag rather than a predicate, and so its overuse, along with bit masks, is a prime candidate for being listed as a 'SQL Code Smell'. Joe Celko makes the case. … Read more
22 November 2010
22 November 2010

Contiguous Time Periods

It is always better, and more efficient, to maintain referential integrity by using constraints rather than triggers. Sometimes it is not at all obvious how to do this, and the history table, and other temporal data tables, presented problems for checking data that were difficult to solve with constraints. Suddenly, Alex Kuznetsov came up with a good solution, and so now history tables can benefit from more effective integrity checking. Joe explains... … Read more
28 October 2010
28 October 2010

VALUES() and Long Parameter Lists – Part II

The use of the comma-separated list of parameters to a SQL routine, that Phil Factor calls the 'comedy-limited list, is a device that makes seasoned SQL Database developers wince. The best choice of design for passing variable numbers of parameters or tuples to SQL Routines varies according to the importance to you of SQL Standards. Joe Celko discusses the pros and cons of the front-runners … Read more
08 October 2010
08 October 2010

State Transition Constraints

Data Validation in a database is a lot more complex than seeing if a string parameter really is an integer. A commercial world is full of complex rules for sequences of procedures, of fixed or variable lifespans, Warranties, commercial offers and bids. All this requires considerable subtlety to prevent bad data getting in, and if it does, locating and fixing the problem. Joe Celko shows how useful a State transition graph can be, and how essential it can become with the time aspect added.… Read more
01 September 2010
01 September 2010

The DIS-Information Principle, Part II

Database design simply involves populating a schema with tables that model sets of entities and relationships. A table will contain Columns that model an entity's attributes and contain scalar values. What could go wrong? Plenty, unfortunately, when these simple principles are misunderstood or flouted, and Joe continues to itemise bad design practices that can cause subsequent grief for the application developers.… Read more
22 July 2010
22 July 2010

VALUES() and Long Parameter Lists

To make progress as a relational Database programmer, you have to think in terms of sets, rather than lists, arrays or sequential data. Until that point, you'll feel the need to pass lists, arrays and the like to functions and procedures. Joe suggests some unusual ways around the difficulty and broods on the power of the VALUES constructor.… Read more
16 October 2009
16 October 2009

Geek of the Week: Joe Celko

Joe Celko, the Database Developer and writer from Austin Texas, is not a man to mince his words. His encyclopedic grasp of SQL and relational Databases in general comes from a mix of academic knowledge and practical experience. In discussions he can be fascinating, cantankerous, amusing and satirical, but he is never ever dull, as Richard Morris found out when we sent him to interview the SQL language's most famous advocate.… Read more