In the past, most discussions of the emerging Universal Server architecture have tended to focus on such esoteric topics as this new platform's ability to manage spatial data, images, time series, World Wide Web pages and other high-end functions. That's not surprising given the fact that many RDBMSs simply cannot handle these rich, complex and increasingly common new data types. The Universal Server, with its demonstrated scalability, robustness and extensibility, has opened a clear and upward pathway to the data management solutions of the future. But while this exciting new platform promises to solve the data challenges of tomorrow, we should also understand that the Universal Server delivers practical and highly economical solutions to the more common data problems of today.
Let's take a closer look at some of the everyday needs of the database manager and how the Informix Universal Server architecture meets these immediate requirements.
Case-Insensitive Searches
Although we live in an upper- and lower-case world, most relational databases in use today are, in fact, poorly equipped to deal with case- differentiating assignments.
One very limiting feature of existing RDBMSs is that they can typically return results for only the most precise and case-specific searches. In one common situation, we might create a field in a record, which in a relational database would be a column in a table, and we might define this field as LASTNAME to be 30 alphanumeric characters in length. If for example we enter "McDonald" into this LASTNAME field, then want to search for and find this entry, we must specify this precise spelling and case treatment. A search for "Mcdonald" or "MCDONALD" will yield imperfect results. And if the original entry was keyed in as "Mcdonald," a standard case- sensitive RDBMS search would ignore this data entirely.
This limitation creates frustrating problems when databases include entries requiring multiple words or descriptions or on addresses, which for mailing list purposes must be printed with correct upper- and lower-case formats. Surprisingly, while it is simple enough to add an extension to the SQL to handle case variations, with the exception of Oracle and a few other products, very few relational databases can deliver case-insensitive search results. In fact, rather than truly solve the puzzle, most systems simply require that text be entered in an all upper-case format.
The Universal Server, fortunately, provides a simple and elegant solution to the need for case-insensitive searches. Because the Universal Server allows us to define new data types from scratch or by inheriting previous structures, we could create a new data type, such as "ICChar" (for "Ignore Case" Character) and make the LASTNAME column/field described be of that type. Data entry personnel can now input the information in the case format, but the data is stored in correct upper- and lower-case format, without worrying that users will be unable to find ALL potential case variations.
Sort Order problems
Case-driven limitations also cause sorting difficulties in relational databases. Because traditional DBMSs prioritize characters by both alpha and case, a normal sort lists all returns from capital A through capital Z, then all lower-case results from "a" to "z." This is obviously not the outcome needed when printing out a list of names or other items. What we typically need is a true case-insensitive alpha sort; but, in fact, there is not a relational database in existence today that can deliver this common-sense result.
Once again, the extensibility of the Universal Server allows us to craft a clean and very workable solution to the need for case-insensitive sorts. Universal Server requires a comparison function for new data types. The function should return -1, 0 or 1 depending on whether a value is less than, equal to or greater than the value that it is being compared to. Since this function is user provided, we could supply a comparison function that ignores case when doing the comparison. Because Universal Server uses the comparison function for searches, ordering and index building, it is possible to quickly build the case-insensitive sorts needed in literally hundreds of day-to-day database tasks.
The SOUNDEX Variable
A third text-related shortcoming of most relational databases is the inability to deal effectively with certain phonetic or "sounds like" searches. Many databases contain text with identical pronunciation but significantly different spellings, and users may often need to search for and find all of these potential variations. RDBMSs are unable to provide efficient "sounds like" search capabilities at either the client or server locations.
Fortunately, the Universal Server provides a unique and amazingly simple solution. By applying the SOUNDEX public domain algorithm, which basically converts any text-based expression into a corresponding number, the Universal Server can return case-insensitive search results showing every entry that "sounds like" the specified search parameter. It does, of course, require some planning and effort to set up the SOUNDEX-enabled field. But only the database developer is aware of the existence of these mirror-image SOUNDEX character and integer values in the field. The user sees only the normal text entries or the results of a case-insensitive "sounds like" search. This unique SOUNDEX-enabled capability provides managers with yet another potent data management tool.
Dealing with Hierarchies
Hierarchies are a staple of virtually every business information system, because managers know that data is most useful when it is compartmentalized by its most logical relationships. Two classic examples of hierarchies might be clients arranged by country, region, district and city, or a final product composed of assemblies, sub-assemblies and finite components. In the most basic of applications, these hierarchies are simple and change very little over time. But in the vast majority of real-world business situations, hierarchies are complex and fluid structures which must change quickly and easily to meet the contingencies of the competitive marketplace.
MRP (Manufacturing Requirements Planning) and JIT (Just-in-Time) inventory strategies require the ability to instantly adjust both the structure and content of the business database. Take, for example, a database used to track and manage a computer monitor product line. The master or parent group of the hierarchy is the total monitor package, while descending "children" tables contain data on the monitor's various assemblies, components and parts. Since costs originate at the level of the most basic materials and value-added service, a change in the price of any component affects the total cost of the monitor and must be tracked and factored accordingly.
Unfortunately, in a traditional relational database, it can be extremely difficult to create and structure the number of interrelated tables needed to track and retrieve information on a complex product line comprised of numerous sub- assemblies. And it can be virtually impossible to expand or restructure these complicated table relationships to accommodate the inevitable changes in a product line's component parts.
Database managers who have struggled for years to adapt RDBMSs to meet their changing hierarchy requirements have found in the Universal Server a powerful and flexible solution. Universal Server has the unique ability to create new data types, which, in this case, means a data type with the single function of managing a hierarchy structure. This "hierarchy data type" is configured exclusively to assemble, show and manage hierarchical relationships. Once this hierarchy data type is established, the Informix Universal Server model allows us to inherit derived types from that base type. This derived type could be extended with columns such as descriptions, costs, units, regions, etc.
The result is a full-featured hierarchical database that is flexible, scalable and highly manageable. The Informix Universal Server, with features expected from later releases and third-party developers, will allow managers to define and use complex new data types and to perform data encapsulation, aggregate functions and other advanced operations.
The unique capabilities of this emerging Informix platform give database managers the tools they need to meet the information demands of the future, while providing in-place solutions to some of the more common data challenges of today.
J.D. Hicks co-founded Virtual Solutions, a Metamor Worldwide, Inc. company, in 1993. As chief technology officer for Virtual Solutions, Hicks is responsible for directing the research and development of the company's concepts, specifications and applications. For more than a decade, he has been involved in the design, development and support of UNIX database systems. He is knowledgeable about leading data- structures, database design methodologies and the practical use of popular file handlers.
Be the first to comment on this post using the section below.