Posted by Daigoro Toyama on October 14, 2011
Dai analyzes the pros and cons of the relational and network database model. He discusses how each model works and highlights the strengths, weaknesses and capabilities within each model. The differences between these two models can lead to the success or failure in developing an application.
The following discusses how each model works and highlights the strengths, weaknesses and capabilities within each model. The differences between these two models can lead to the success or failure in developing an application.
Relational Database Model
You are sitting on the bus, headed home. A little tired but not all that sleepy, you decide to listen to some Bon Jovi. On second thought, you want to watch one of his movies (Cry Wolf comes to mind). The iPhone’s spotlight search is nice. Type in “Bon Jovi” as the keyword, and the media player will seamlessly find both their music and movies – two different categories – stored on the device.
Common operations like the ones above usually make use of a relational database management system (RDBMS). The database, let’s call it Media Collection, defines three tables ARTIST, ALBUM and MOVIE where the names of the artists, albums and movie titles are stored respectively. When a particular artist is selected, like Bon Jovi in our example, a SQL query is issued to retrieve all the albums and movie titles that belong to the selected artist. The data returned is displayed on the iPhone screen, usually in alphabetical order.
Relational Data Model Weaknesses
These seemingly simple steps reveal two fundamental weaknesses inherent with the relational data model. The first weakness is the fact that each relationship requires duplicate columns in both tables associated with it. For example, both the ARTIST and ALBUM tables must contain and thus maintain a column that stores the names of the artists so a link between an artist and their albums can be established. This means extra storage space as well as programming overhead are required to keep the two columns “in sync.” Changing the name of an artist means that all the ALBUM entries for that artist need to have their “artist name” column updated.
The second weakness, and the more relevant aspect of this article, is the fact that a single relationship may only contain two tables, the primary key (main) table and the foreign key (referencing) table. Within the Music Collection database, both the ALBUM table and the MOVIE table need to reference the ARTIST table. Due to this limitation, two separate relationships need to be defined; one that includes the ALBUM table reference to the ARTIST table and one that has the MOVIE table referencing the ARTIST table.
Inefficient Data Retrieval Operation – Relational Data Model
This translates into a rather inefficient data retrieval operation when finding all the albums and movies associated with an artist. During the first operation, the database system retrieves all the related albums from the ALBUM table and stores the result set in a temporary location. During the second operation, the same process as the first is performed, only this time it retrieves results from MOVIES. The final operation merges the two result sets, re-orders them if necessary, and then returns the merged result set.
The inefficiency of the relational model may not be a showstopper when the amount of data in the database is relatively small and there is abundant computing resource available, especially since it is not uncommon today for an average person to own a computer with a 2GHz dual-core CPU with 2GB of memory and a 500GB hard disk. On the other hand, there exists a rapidly emerging market for small computers that require a database system (DBMS) – smartphones, portable music players, and GPS devices, to name a few. With limited CPU speed and memory, these hand-held systems can benefit greatly from a DBMS that allows for flexible and efficient data storage and retrieval in terms of both performance and disk usage.
The network database model offers exactly that.
Network Database Model
An enhanced form of the hierarchical data model, the network model represents data in a tree of records. Relationships between tables (records) are expressed as sets. A set has one parent record (owner) and one or more child records (members). Related records in a set are directly linked by pointers, rather than by matching duplicate columns as is the case in the relational data model.
Records Associated with a Single Owner
The network database model allows records from more than one table to be associated with a single owner record of another table. This provides a definite advantage over the relational counterpart when querying results from multiple foreign-key tables associated with one primary-key table. In the Media Collection database, both the ALBUM and MOVIE records can also be members of the ARTIST record in one set, as shown in Figure 2. This means that both albums and movies for a given artist can be retrieved in a single operation. This eliminates the need to store and potentially re-order temporary results in the middle of the operation, resulting in better query performance. Without the need to store and maintain duplicate columns network databases also help reduce disk space and memory usage.
Performance Case Study
Real-life data shows that the performance gain and resource savings of using network databases can be quite significant. In a data structure using a three-way relationship among the ARTIST, ALBUM and SONG tables, our developers compared the data modification and query performance of the relational and network database models using both desktop systems and small, consumer devices. They found that the network model used 29 percent less disk space to store the same number of records and relationships than the relational data model. All the storage savings can be attributed to replacing the ARTIST-ALBUM and the ALBUM-SONG foreign-key indices with set pointers.
Removing these data structures had a huge effect on the storage requirements because a typical B-Tree index requires approximately 1.3 times the space of what it indexes. They also discovered that the network database model achieved up to 23 times better insert performance and up to 123 times faster query performance, as shown in Table 1.
Different management requirements mean different data structures and different methods of storing and accessing the data. The resulting system may consist of a few tables with no relationships, or hundreds of tables associated with complex relationships. While the relational data model is the de facto standard, we now know that it does not always provide the optimal solutions for more complex data management problems. Selecting the appropriate data model, or even combining multiple models, can produce a far more efficient result than the relational data model alone. The result is significant cost savings, improving quality and an enhanced user experience.
Conclusion – Network Model for Speed, Relational for Usability
While the relational data model is very popular because of its ease of use, it requires key and index tables which drastically slows down an application. The network database model provides faster access to the data and is the optimal method for a fast application. So if you click on your favorite artist and see the list of their 20-plus albums and movie titles in a split second on your media player, it may just be driven by a network-model database engine under the hood.