Principles of System Design — Part 2

Perigeon Software
5 min readJun 29, 2022

Applications require long-term storage for the user or application-specific data. Arrays in-memory data structures such as linked lists are optimized for CPU access through pointers. Permanent storage is designed for read/write access by clients/processes that connect to the database server. Data modeling is a critical component of long-term persistence. This post will focus on how to select a solid data model for your application.

Relational Database

The most well-known and widely used data modeling strategy is the use of relational tables. Data in relational tables are organized into table records. Tables are linked together using a primary key and a foreign key. There are several reasons why you should use a relational table.

  1. You are still developing v1 of your software, and data access patterns are not yet evident. Relational schemas are always a decent initial option. All apps are basically bad (as a senior backend engineer at my office correctly informed me:)), and no one would judge you for beginning with relational data rather than sophisticated NoSQL or Graph DB.
  2. Strict schema on write constraints must be enforced.
  3. You want to keep data redundancy to a minimum. Normalization of schema in relational models shreds information into multiple tables.
  4. Many to one and many to many relationships exist in your data model. In other words, you know ahead of time which joins will be executed. One of the most important secrets of relational databases is the ability to join and query relational databases using a declarative language like SQL. A great deal of study and work has gone into making relational queries as quickly as possible. An application developer just has to indicate the query’s intended data pattern. To fetch/write data, the query engine will turn the SQL query into optimized code

NoSQL Database

Object-oriented programming is used in application development. When data is stored in tables, however, a translation from objects to “shredded” relational tables is necessary. ORM frameworks give boilerplate code to ease translation effort, but there is still work to be done.

NoSQL overcomes this issue by storing each record as a self-contained JSON document. Assume we need to keep patient demographic information in addition to his present conditions. In a NoSQL database, this record may be represented as follows:

"first name": "John," "last name": "Doe," and "conditions": "none."

or

["name": "T2DM", "onset": "December 12, 1990"]

Because all data is contained within a single NoSQL document, you do not need to make several joins if you need to read profile data for a patient in your app. Using a NoSQL database may make more sense if your data model has a tree-like, one-to-many connection.

What if, in the patient example above, we wanted to store ICD10 standard codes for conditions rather than the name of the condition? This is more difficult with document-based NoSQL databases, which have limited support for joins. You can still do a join at the application layer, but it will always be inferior to joins performed at a conventional relational database layer. In this circumstance, NoSQL databases become less appealing.

Finally, if your data model does not have a set schema, using NoSQL may make more sense. Consider the patient example above, and suppose we additionally need to record the patient’s date of birth. In the case of NoSQL, we may add a new field, ‘dob,’ to new documents. We may also implement code at the application level to handle reading old documents lacking the dob field. In a relational database, the way to deal with dob would be to change the schema and do data migrations. Data migrations are time-consuming and involve downtime, thus they are normally avoided.

Graph Database

When your application’s data architecture requires many ‘many-many’ relationships, a graph database makes a lot of sense. The relational model can manage a few many to many relationships, but after a certain point, all relational joins become clumsy and sluggish. Using graph databases also has the added benefit of making it simple to expand links between disparate things.

A graph is made up of two types of objects: nodes and edges. Nodes include object or entity descriptions. Edges describe the connections between nodes. Assume a person has an allergic response as a result of being exposed to a material. As nodes, you may represent a person, an allergic response, and a chemical.

A graph database may also be used to model relationships between nodes. A relationship between a person and an allergy, for example, might be a unidirectional relationship “person-has-allergy” from the person node to the allergy node. The link between allergy and substance can be a unidirectional “triggered-by-exposure-to” relationship from allergy to substance.

Why not use relational tables for this? You certainly can. You may put up the proper Pk-FK restrictions on three tables: person, allergy, and substance. As I previously stated, employing graph databases makes sense when we have a large number of many-many links. Assume you include a location object into the general scheme of things in order to record the location of the individual where the allergic response happened. A neighborhood, city, state, nation, continent, or hemisphere can all be considered locations.

Essentially, location data may be obtained at varying degrees of resolution. It will be difficult to write a declarative query using SQL. SQL must know ahead of time which joins will be used in the query. In contrast, in a graph database, you can visit several nodes and edges before arriving at the target node. Using a graph database declarative query language like Cypher for the Neo4j graph database, you may describe the fact of traversing a graph once or many times rather simply.

Perigeon Software is a software development firm. With a fresh perspective and dedicated attention to each client, we provide a complete IT solution globally. By defining, designing, and developing solutions tailored to meet our clients’ business objectives, we ensure that our clients get the maximum return on their investment and support them in tackling evolving business and technological issues. Our mission is to provide the best customer service and expertise using the most practical and robust web technologies/software technologies to satisfy our clients’ IT objectives and to provide them with the business and competitive advantage they needed to succeed.

To learn more about Perigeon’s portfolio, visit: https://www.perigeon.com/portfolio/

To learn more about Perigeon’s Salesforce capabilities, visit: https://www.perigeon.com/salesforce/

Drop us a mail at possibilities@perigeon.com to discuss your salesforce requirement.

Originally published at https://www.perigeon.com on June 29, 2022.

--

--

Perigeon Software

We deliver customized global IT solutions using cutting-edge technology with expertise in Salesforce, Shopify, BigCommerce Data and AI.