Modeling collections in databases: Relational/SQL vs TypeDB

Introduction

Databases excel at handling traditional data types such as numbers and strings, but they lack robust mechanisms for working with complex data structures like collections (e.g., lists and sets).

In this blog post, we explore the difficulties encountered when storing data as collections in databases. In particular, we’ll focus on the differences between relational database and TypeDB.

Whereas modeling and querying collection in SQL can be difficult due to a lack of flexibility and performance issues, TypeDB supports an alternative approach that embraces the inherent relationships and structure of collections.

We will delve into the different strategies of implementing collections in databases, uncovering the contrasting strategies associated with relational databases and also withTypeDB. By understanding these differences, you’ll be equipped to navigate the challenges and make informed decisions when managing collections in your database.

Relational

While relational databases are primarily designed for tabular data, there are several approaches to efficiently store and manage collections of elements. In this article, we’ll explore them and discuss the potential problems with each approach.

Approach 1: use a separate table

One common approach is to create a separate table to store the elements of a collection. Each element occupies a row in the table and is linked to a parent table using foreign keys. This approach offers flexibility and allows for efficient querying and manipulation of individual elements in the collection. However, it requires additional table and key management.

CREATE TABLE ListOwner (
  owner_id INT PRIMARY KEY,
  list_name VARCHAR(255)
);

CREATE TABLE ListItems (
  item_id INT PRIMARY KEY,
  list_id INT,
  item_name VARCHAR(255),
  FOREIGN KEY (owner_id) REFERENCES ListsOwner(owner_id)
);

SELECT ListOwner.owner_id, ListOwner.list_name, ListItems.item_id, ListItems.item_name
FROM ListOwner
JOIN ListItems ON ListOwner.owner_id = ListItems.owner_id;

Approach 2: use a delimited string

Another approach is to store the collection as a delimited string within a single column. This method is suitable when the collection is of a fixed size or the order of elements is not meant to change. While this approach is simple to implement, it makes it harder to perform operations on individual elements within the collection. Splitting and parsing the delimited string can be cumbersome, and querying becomes less efficient.

CREATE TABLE Lists (
  list_id INT PRIMARY KEY,
  list_elements VARCHAR(1000)
);

Approach 3: use JSON or array data types

Some databases provide JSON or array data types that allow storing collection directly. This approach offers flexibility and ease of manipulation. You can perform operations such as adding, removing, and searching for specific elements within the collection. However, not all database systems support JSON or array data types, and there might be limitations on indexing and querying nested elements within the collection. The following example uses JSONB column, which is supported in PostgreSQL and CockroachDB.

CREATE TABLE Lists (
  list_id INT PRIMARY KEY,
  list_elements JSONB
);

Considerations and potential problems

Regardless of the chosen approach, there are a few considerations and potential problems to keep in mind when storing collection in SQL.

Query complexity

Storing collection as separate tables or using JSON data types can simplify querying and manipulation. On the other hand, storing collection as delimited strings often requires complex string operations, making queries more convoluted and less efficient.

Data integrity

With the separate table approach, you can ensure data integrity by using foreign keys to link the collection elements to a parent entity. However, with delimited strings or JSON data types, maintaining referential integrity becomes more challenging, and you might encounter issues such as orphaned or inconsistent data.

Indexing and performance

Efficient querying is crucial when dealing with collection. Depending on the approach chosen, indexing might be limited or more complex. Delimited strings, for instance, do not support indexing on individual collection elements. JSON data types might face performance issues when dealing with large collection or deeply nested elements.

Flexibility and extensibility

Consider the future requirements of your application. If you anticipate the need for advanced querying or modifications to the collection structure, using a separate table or JSON data types can provide more flexibility and extensibility compared to delimited strings.

Storing collection of elements in SQL databases requires careful consideration of the application’s requirements and constraints. While various approaches exist, each has its trade-offs. Using a separate table, employing delimited strings, or leveraging JSON or array data types are all viable options, but they come with their own challenges.

TypeDB

In TypeDB, one effective approach to storing collection of elements is to model membership of each element as a relation. By creating a relation type specifically for elements in a collection and creating relationships between the collection and the objects to be stored in it, you can represent the collection as a set of connected entities. This approach provides flexibility and allows for efficient querying and manipulation of individual elements within the collection.

define

object sub entity,
  plays element:contains;

collection sub entity,
  plays element:in;

element sub relation,
  relates in,
  relates contains;

Using the same approach (and the same schema), we can also create a collection by having multiple objects play the contains role in a single element relation.

We could even take it one step further by removing the collection entity and using just a relation to represent a collection.

define

element sub entity,
  plays collection:contains;

collection sub relation,
  relates contains;

Challenges and Considerations

Data model complexity

Storing collections as relations increases the complexity of the data model. Properly designing the schema and establishing the relationships between the collection and its elements requires careful consideration. You need to define the appropriate roles, cardinalities (soon), and relationship role players to ensure the integrity and accuracy of the data model.

Query performance

Retrieving and manipulating individual collection elements in TypeDB requires traversing the relationships. TypeQL is optimized to query interconnected data efficiently, but large lists can still introduce performance overhead. It’s essential to optimize your queries using subtypes. However, regardless of the performance, querying collections is quite simple. We just have to specify the relation which connects elements as a collection.

# This query would require an extension of the schemas presented
# in the previous section to integrate "name" attribute.

match
  $coll isa collection, has name "Friends";
  $el ($coll, $obj) isa element;

Data integrity

Ensuring data integrity can be challenging when dealing with collections of elements. It’s crucial to enforce consistency and avoid orphaned or inconsistent data. Maintaining the relationships between the collection and its elements when adding, updating, or deleting elements is essential. With the proposed approach, this is not a problem as the schema is really simple.

Schema evolution

As your application evolves, the structure of your collections may change. Adding or removing elements from a collection or modifying the collection’s schema requires careful consideration to maintain compatibility with existing data. Anyway, being able to subtype the original collection to adapt to new needs is a very powerful feature.

Conclusion

While relational databases and SQL have long been the de facto standard for data management, they fall short when it comes to modeling and querying interconnected data such as collections.

By leveraging the unique features of TypeDB, developers can build robust applications that efficiently handle complex relationships and use the full potential of their data.

TypeDB provides powerful query capabilities specifically designed for traversing complex relationships, including nested collections. This means you can effortlessly retrieve subsets, filter based on conditions, and perform advanced operations like intersection, union, and difference on collections in TypeDB.