There are 2 fundamentally different approaches to database design or schema.
Non-programmers can find it hard to understand the dramatic distinction between relational and object-oriented database designs or schemas.
A relational database schema
A relational database is conceptually the simplest. It is one made up of files (or tables) with relationships to records (or rows) in other files (or tables).
Each record in a file (or row in table) has space for the same information. There can be as many records or rows as required. It is like a spreadsheet and can be created with a tool such as Excel. That is why the words “files” or “tables” get used interchangeably.
Relational database design assumes that the designer understands the use of the database thoroughly, can include places for all of the information that may be required, and can define the information within fairly narrow, simple types (typically numbers, strings, dates, times or Booleans1).
An object-oriented database schema
An object-oriented database is unlike a relational database. It is made up of objects or classes, but an object or class does not typically store information. Instead, an instance of the object is made “persistent” to store the data.
The basic data types are available. However, some objects or classes may be complex data types that can be “instantiated” in other objects. Properties or values are not limited to simple types, as in a relational database. The designer is free to create non-standard data types and procedures (for example: age = today – birth date, or something far more complicated).
The designer might start with a basic object, such as a person, which has properties appropriate to all kinds of people. She/he may then create one or more descendant objects (like “employee” or “contact”) with additional properties that are unique to each.
The application then creates a persistent instance of an employee or contact, which is retained. But there is little or no intrinsic support for querying.
An object-relational database schema
A third type, called an “object-relational database”, combines both object-oriented and relational features to provide the easy access of a relational system and, like object-oriented data modelling, including complex data types and methods like “age = today – birth date”, as mentioned above.
As with a relational database, the information can be manipulated with queries using a conventional query language, like Sequential Query Language (SQL).
As with an object-oriented database, the database is a persistent object store for software written in an object-oriented programming language.
An object-relational database allows software developers to integrate their own types and methods in the database.
Why does it matter?
Relational databases have been around for a long time, and most application development environments, thanks to open database connectivity1 (ODBC) have intrinsic support for them.
In order for an application to take full advantage of an object-oriented database, it typically needs to be written in an object-oriented language (like Java), which is fairly difficult to do. Programmers who can do it are hard to find and typically more expensive.
How are they used?
In an address book, for example, a relational database is appropriate because a specific set of information is retained for each contact (First name, Last name, Home phone, Work phone, Mobile phone, Home address, Work address,…). They are all pretty standard.
In situations where it is not clear from the start exactly what information may be required, an object-oriented database is appropriate. New objects or classes can be created to hold information, and new methods can be created “on the fly” to expedite a task. But the application or program necessary to gain this benefit is harder to build (and consequently more expensive) and maintenance can be more difficult (and consequently more expensive).
An object-relational database has the attraction of being accessible using the same application development environments as a relational database, along with object-oriented languages. It can support procedures and complex data types. The disadvantage is that its flexibility has to be constrained in the interests of simple accessibility.
How are the different types implemented?
All databases are accessed through a program or application.
A relational database can be implemented with varying degrees of security by a database engine, which may be either home-made or commercially available.
To get the true benefit of an object-oriented database, the application needs to be written in an object-oriented language, by a specialist programmer.
An application designed to use an object-relational database does not necessarily require a specialist programmer, although such a person can do parts if necessary. However, the bulk of it can be done by the same programmer who would build an application for a relational database.
Why does it matter?
If you have used iTunes or Windows Media Player, you have seen excellent implementations of relational databases. Both let you decide how you want to categorize and sort your media library.
If your company spent lots of money on a database that stopped working on January 1, 2000, no longer meets the company’s needs, or will not run on available hardware, you have seen the worst.
The decision matters in terms of longevity, usability and value for money.
1 ODBC was originally developed by Microsoft during the early 1990s, following the previously developed Boreland Database Connectivity model. The supplier provides an application program interface (API) which is associated in a windows application (the ODBC Administrator) with a particular database file and given a data source name (DSN). The programmer then connects to that DSN and passes instructions in sequential query language (SQL). This is often spoken by programmers as if it were spelt “sequel”.
