Clarity in Complexity: Why DataJoint’s Five Query Operators Are All You Need

Navigating complex data demands tools that offer both power and clarity. DataJoint is designed for building and managing scientific data pipelines. The upcoming release of DataJoint Specs 2.0 marks the first time DataJoint will be developed against a formal, open specification document, embodying the philosophy of an open standard and capturing its core theoretical concepts to ensure consistent implementations across different platforms.

While many are familiar with SQL, the lingua franca of relational databases, DataJoint’s query language, as defined in these new specs, employs a remarkably concise set of just five core operators. This naturally begs the question: in a world accustomed to SQL’s extensive vocabulary, can just five operators truly be enough? This article argues an emphatic “yes” – not despite their small number, but precisely because of their rigorous design and unwavering commitment to fundamental relational principles.

The Theoretical Bedrock, ERM’s Vision, and SQL’s Journey

To appreciate DataJoint’s approach, it helps to understand the foundations. Relational database theory, pioneered by Edgar F. Codd in the late 1960s/early 1970s, is built on rigorous mathematics. Codd introduced relational algebra, a procedural language where operators like selection, projection, and join manipulate tables (relations) to produce new tables. He also defined relational calculus, a declarative language allowing users to specify what data they want. Codd proved these two formalisms were equivalent in power, establishing the concept of relational completeness.

In 1976, a pivotal moment in conceptual database modeling arrived with Peter Chen’s introduction of the Entity-Relationship Model (ERM). Chen proposed a way to look at the world, and thus model data, in terms of “entities” (distinguishable “things” like a student or a course) and “relationships” between them (like a student “enrolling” in a course). The ERM provided a powerful visual language—ER diagrams—that became incredibly influential for database schema design and for communication between database designers, domain experts, and stakeholders. It offered an intuitive framework for translating real-world scenarios into structured data models, naturally leading to well-normalized schemas.

However, a significant disconnect emerged. While ERM became a standard for conceptualizing and designing databases, its elegant, entity-centric syntax and explicit relationship constructs were never directly mirrored in SQL’s Data Definition Language (DDL for creating tables) or its Data Query Language (DQL for retrieving data). SQL’s CREATE TABLE statements, while defining columns and foreign keys (which implement ERM relationships), don’t speak the direct language of “entity sets” and “relationship sets” in the way ERM diagrams do. Similarly, SQL’s JOIN syntax, while powerful, doesn’t inherently guide users to join tables based on the semantically defined relationships from an ERM perspective. This left a gap between the clarity of the conceptual design and the often more intricate, attribute-level syntax of SQL implementation and querying.

SQL itself emerged as a practical implementation drawing from both relational algebra and calculus. Its SELECT...FROM...WHERE structure has a declarative feel whereas JOIN is a relational algebra operator. A fascinating part of SQL’s early vision was its aspiration to be a natural language interface for databases, aiming for queries that read like English prose. While admirable, this came at the cost of the explicit operator sequencing and rigorous composability found in more formal algebraic systems.

SQL’s widespread adoption, fueled by successful standardization efforts, has been immensely beneficial. However, through its evolution, SQL accumulated “conceptual baggage”—layers of complexity and ambiguity that can obscure the underlying simplicity of relational operations.

The Cornerstone: Well-Defined Query Results

A central tenet of the DataJoint philosophy, crystallized in the new Specs 2.0, is that all data, whether stored in base tables or derived through queries, must represent well-formed entity sets (or relations). What does this mean in practice? It means that every table, including any intermediate or final result of a query, must:

• Clearly represent a single, identifiable type of entity (e.g., “Students,” “Experiments,” “MeasurementEvents”).
• Have a well-defined primary key – a set of attributes whose values uniquely identify each entity (row) within that set.
• Ensure that all its attributes properly describe the entity identified by that primary key.

This commitment is upheld through what the DataJoint Specs refer to as algebraic closure. Each of DataJoint’s query operators is designed such that if you give it well-formed relations as input, it will always produce another well-formed relation as output, complete with its own clear primary key and entity type.

This brings us to a critical question to keep in mind when working with SQL: Can you always tell, just by looking at the SQL statement, what real-world entity each row in the result is meant to represent, and what makes each row unique? Often, the answer becomes murky.

DataJoint’s “Fab Five”: A Modern Interface to Relational Power

With deep reverence for Codd’s foundational genius, it’s fair to ask if the original set of algebraic operators, defined over half a century ago, still constitutes the optimal user-facing interface for today’s data challenges. DataJoint, guided by its new Specs 2.0, proposes a refined, modern set of five operators designed for clarity and power:

1. Restriction (&, -): This is your precision filter. It selects a subset of rows from a table based on specified conditions without altering the table’s structure or primary key. The resulting table contains the same type of entities and the same primary key.
2. Projection (.proj()): This operator reshapes your view of a table by selecting specific attributes, renaming them, or computing new attributes from existing ones. Crucially, the primary key of the original table is preserved, ensuring the identity of the entities remains intact.
3. Join (*): This operator combines information from two tables. But it’s not just any combination; it’s a “semantic join” (more on this later) that ensures the resulting table represents a meaningful fusion of entities, with a clearly defined primary key derived from its operands.
4. Aggregation (.aggr()): This operator can be seen as an advanced form of projection. For a table A, A.aggr(B, ...) can perform the same functions as A.proj(...)by selecting, renaming, and calculating attributes. Additionally, it can also calculate new attributes for each entity in A by summarizing related data from table B. The beauty is that the resulting table still has A‘s primary key and represents entities of type A, now augmented with new information.  Despite their similarity, we consider .proj and .aggr as distinct operators.
5. Union (+): This operator combines rows from two tables, A and B. For this to be valid, A and B must represent the same type of entity and share the same primary key structure; the result inherits this structure.

These five operators, through their strict adherence to producing well-defined results, form the backbone of DataJoint’s expressive power.

Untangling SQL: Where Simplicity Meets a Wall of Operators (Illustrated)

Let’s look at how DataJoint’s approach contrasts with SQL in practice.

SQL’s Operator Count – A Fuzzy Number

How many “operators” does SQL effectively have? It’s notoriously hard to quantify because many distinct logical operations are bundled into the complex SELECT statement. A single SELECT can perform filtering (like DataJoint’s restriction), column selection and computation (projection), table combination (join), grouping, and ordering, all intertwined.

Furthermore, seemingly simple modifiers in SQL can act like entirely new, transformative operators. Adding DISTINCT to a SELECT query doesn’t just remove duplicate rows; it fundamentally changes the resulting relation, implying a new primary key based on all the selected columns. Similarly, aggregate functions like COUNT() or AVG() within a SELECT statement, with and without a GROUP BY clause, transform the output into a new type of entity (e.g., “summary per department” instead of “employees”), with the grouping columns forming the new primary key. If we were to “unroll” every distinct transformation SQL can perform, the operator count would be vastly larger and far more entangled than DataJoint’s explicit five.

The SELECT Statement’s Hidden Logic

The order in which SQL clauses are written (SELECT, FROM, WHERE, etc.) doesn’t reflect their logical execution order. This “hidden logic” often confuses users, particularly regarding the scope of aliases defined in the SELECT list. DataJoint’s explicit, sequential application of operators avoids this ambiguity entirely.

The Labyrinth of SQL Joins vs. DataJoint’s Semantic Precision

SQL offers various join implementations like INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN with various modifiers such as NATURAL, USING, and ON <condition>.  Classical relational algebra also defined foundational concepts such as equijoin (joining based on equality, a specific type of a more general “theta join” which allows any comparison – though these terms are more academic than direct SQL syntax) and natural join, which have influenced SQL’s join logic. However, SQL’s NATURAL JOIN (matching on identically named columns) can be treacherous, as it may join attributes that share a name but have completely different meanings.

The ERM guided that meaningful joins should occur on foreign keys between related tables. DataJoint institutionalizes this with Semantic Matching for its one and only join (*) operator. For attributes to be matched, they must not only share the same name but also trace their lineage through an uninterrupted chain of foreign keys to the same original attribute definition. If identically named attributes don’t meet this criterion, it’s a “collision,” and DataJoint raises an error, compelling the user to explicitly rename attributes using projection before the join. This rigor, tied to the foreign key relationships typically visualized in a schema diagram, means the validity of a DataJoint join is often apparent from the schema structure itself.

Semijoin and Antijoin: The Illegitimate “Joins”

Speaking of joins, relational algebra textbooks often discuss semijoin and antijoin.

• A semijoin (A⋉B) returns rows from table A for which there is at least one matching row in table B (based on common attributes), but it only includes columns from table A.
• An antijoin (A▹B) returns rows from table A for which there are no matching rows in table B, again only including columns from table A.

While these are powerful, the “join” in their names is quite a misnomer. True joins combine attributes from both participating tables to form a new, wider entity and create new entities by pairing rows from the joined tables. Semijoins and antijoins, however, don’t do this. They fundamentally act as filters on table A based on the existence (or non-existence) of related records in table B. The structure of table A (its attributes and primary key) remains unchanged; you merely get a subset of its rows. This is precisely the definition of a restriction operation.

In SQL, these operators are implemented in a wide variety of ways, typically using a subquery with EXISTS, NOT EXISTS, IN, and NOT IN operators, or as an inner join followed by a GROUP BY or by using a DISTINCT modifier.

The DataJoint Specs 2.0 acknowledge the true restriction-like nature of these operators directly: when performing a restriction by a subquery (which is conceptually how one table filters another), “The restriction acts as a semijoin (for &) or an antijoin (for -)” . The earlier DataJoint manuscript (Yatsenko et al., 2018) also explicitly deprecated the terms “semijoin” and “antijoin” as misleading and confusing. DataJoint thus correctly categorizes these operations under its versatile Restriction operator, avoiding the potential confusion of SQL needing EXISTS, NOT EXISTS, IN, or NOT IN subqueries to achieve similar effects, which can feel less direct than a simple restriction.

SQL’s OUTER JOINs: A Mix of Meanings

SQL’s OUTER JOIN variants (like LEFT JOIN) often create results that are a jumble of entity types. Some rows might represent a complete pairing, while others represent only one entity, padded with NULLs. After such an operation, can you confidently tell what real-world entity each row in the result is meant to represent?

DataJoint’s Specs 2.0 clearly state its approach: it effectively has no direct “outer join” operator because such an operation typically violates the principle of yielding a single, well-defined entity set with a consistent primary key. Instead, DataJoint’s aggr operator cleanly achieves the common goal of augmenting one entity set with summaries from another, preserving the primary entity’s type and identity.

Redundancy in Restriction in SQL

SQL uses multiple clauses for what amounts to filtering: WHERE, ON (in joins), HAVING (for groups), and LIMIT / OFFSET (for result sets). DataJoint streamlines this with its single, powerful Restriction operator (& and its complement -).

Illustrative Examples: DataJoint vs. SQL

Let’s make these differences more concrete. (Imagine a simplified university database with Student, Course, Section, Enroll, StudentMajor, and Grade tables.)

1. Finding Students Enrolled in Any Class

• DataJoint:

Student & Enroll 

• Result: A well-defined set of Student entities.
• SQL:

SELECT *
FROM Student
WHERE student_id in (SELECT student_id FROM Enroll);

Result: Rows from the Student table, but the logic is more verbose.

2. Counting Enrolled Students per Section

• DataJoint:

Section.aggr(Enroll, n_students='COUNT(*)')

Result: Section entities, augmented with n_students.

• SQL:

SELECT sec.*, COUNT(e.student_id) AS n_students
FROM Section AS sec
LEFT JOIN Enroll AS e USING (course_id, section_id)
GROUP BY course_id, section_id

Result: Requires explicit join and grouping by all parts of Section‘s primary key.

The DataJoint Advantage: Why These Five Excel

DataJoint’s design philosophy demonstrates that true power doesn’t come from a multitude of overlapping commands, but from a concise set of orthogonal, well-defined operators that compose reliably.

• Consistently Well-Defined Results (Algebraic Closure): Every operation yields a predictable, valid table with a defined primary key and entity type.
• Semantic Precision: Binary operations like join are based on meaningful relational links, not just coincidental name matches.
• Composability: Simple, reliable steps can be combined to build sophisticated queries.
• Interpretability: The nature of the data remains clear at every stage of the query.
• Entity-Oriented Focus: The operators encourage thinking in terms of whole entities and their relationships, aligning well with conceptual modeling principles championed by the ERM as opposed to the attribute-oriented focus of SQL.

Conclusion: A Clearer Lens for Data Discovery

SQL’s position as a foundational data language is secure, and its contributions are undeniable. However, for the complex, high-stakes data work found in scientific research and other demanding domains, a query interface that prioritizes conceptual clarity, predictability, and semantic integrity can be transformative.

DataJoint, as guided by its new Specs 2.0, isn’t about minimalism for its own sake. It’s about providing a complete and conceptually sound set of query operators that empower users. By ensuring every operation results in a well-defined entity set and by enforcing semantic integrity in operations like joins, DataJoint aims to strip away ambiguity and allow researchers to interact with their data with greater confidence and insight. It’s a compelling case that sometimes, to see further, we need not more tools, but clearer lenses.

‍

This computational platform is helping labs manage complex data pipelines and laying the foundation for AI-driven discoveries in neuroscience and beyond.

Originally published in Nature (April 2025), this piece explores how DataJoint's computational platform is transforming neuroscience research for labs around the world. The current version, slightly expanded from the original, has been updated to reflect the recent Executive Order on Gold Standard Science, emphasizing reproducibility and transparency as national scientific priorities for the United States.

Click to download a PDF of the original Nature article.

Scientific research is often celebrated for its creative chaos — improvisation in the lab, trial-and-error in the field, the occasional serendipitous breakthrough. But as experiments become increasingly data-heavy, with reams of complex inputs and computational analyses, researchers are struggling to scale their operations while maintaining rigor and reproducibility.

A new proposal, outlined in a recent preprint, aims to change that. By adapting best practices from software engineering to scientific research, a coalition of academic and industry partners hopes to streamline data collection, standardize analyses and accelerate discovery — with structured workflows seen as key to this effort. This effort aligns directly with the recent Executive Order on Gold Standard Science, which prioritizes reproducibility and transparency as foundational standards for federally-funded research.

“The goal, fundamentally, is to produce the same productivity increase in science that we have seen in other disciplines,” says Dimitri Yatsenko, a lead architect of the new ‘SciOps’ framework.

The concept takes inspiration from DevOps, which transformed software development in the 2000s by empowering teams with automated workflows, continuous testing, and seamless code integration. DevOps made it possible to create entirely new industries like cloud computing, e-commerce and streaming.

Academic research needs a similar shift, says Yatsenko, and he has created a data operations platform for scientific laboratories to help make that happen. Known as DataJoint, the platform replaces fragmented data handling processes with an end-to-end computational workflow for data entry, acquisition, processing, analysis and visualization — all unified in a coherent pipeline that unlocks collaboration and integration with artificial intelligence (AI) capabilities.

“A key challenge in data science is to combine computations with data management,” notes Yatsenko, founder and chief scientist of a company that shares the platform’s name. “DataJoint handles that as a single problem,” he says.

Available as an open-source general framework, DataJoint is adaptable for any research discipline, from genomics to climate science. But to date, it has had the most impact in systems-level neuroscience, where data integrity and reproducibility are persistent challenges.

Supporting MICrONS

In 2016, the platform was selected as the data backbone for the neurophysiology component of the Machine Intelligence from Cortical Networks (MICrONS) project — a five-year, US$100- million initiative funded by the US Intelligence Advanced Research Projects Activity to map the detailed structure and activity of neural circuits in the mouse brain. DataJoint’s adoption helped establish its value in managing large-scale, multi-modal neuroscience data.

With an eye toward informing next-generation machine learning models that ‘think’ like brains, the MICrONS team collected petabytes of data from electron microscopy (to map synaptic connections), calcium imaging (to track neural activity) and behavioral studies (to understand functional responses) in mice.

‍

DataJoint enabled the MICrONS team to train a new foundation model by capturing the brain in unprecedented detail. With NIH support, DataJoint has translated the system that powered MICrONS into a scalable platform — bringing the same robust data infrastructure to any lab working at the frontiers of neuroscience and AI.

‍

Another DataJoint pipeline integrated the functional data with structural data from systems like CAVE — which, as reported in Nature, enabled scientists to start teasing apart the computational principles that underlie the function and connectivity of the mammalian brain.

Finishing the job won’t be easy. But with much of the data available via DataJoint and the MICrONS Explorer, researchers can continue to explore new questions about neural computation and circuit dynamics. “I think the MICrONS data will be studied for the next decade or more,” Yatsenko says.

‘Just essential’

Beyond MICrONS, over 100 neuroscience labs worldwide use the platform for data pipelines in studies on neurodegenerative disease, neurotransmitter signaling, visual processing, and stroke recovery. Several large, multi- institutional projects, focused on understanding cognition, olfaction, and behavior, also rely on DataJoint.

Before adopting the platform, researchers often relied on convoluted file naming conventions, custom-written code and incompatible formats that complicated data handling. DataJoint eliminates that chaos by centralizing data management, automating analyses and ensuring consistency — and traceability — across experiments. These capabilities are particularly critical now, as the recent Executive Order elevates reproducibility and transparency from best practices to federally mandated priorities.

“I’m so enthusiastic about it,” says Jacob Reimer, assistant professor of neuroscience at Baylor College of Medicine (BCM) in Houston, who was an early adopter of the platform there. “Everything we do is now immediately accessible and easily located,” he adds. “There’s no other place the data can be.”

Reimer had a front-row seat to DataJoint’s early development. As a postdoc working in the same BCM lab, Reimer watched Yatsenko, then a graduate student, spend his free time, between imaging experiments of calcium signals in the mouse brain, working on building a better way to manage the growing data deluge.

At first, most colleagues ignored or outright dismissed Yatsenko’s effort. “Nobody was convinced it was worthwhile,” says Reimer. But then Yatsenko unveiled an early prototype, and, quickly, recalls Reimer, “it became, like, just essential.”

The entire lab soon implemented the platform. So too did collaborators from California and Germany. And by 2016, as interest spread, Yatsenko, together with Reimer and two other members of the same BCM lab group, decided to the DataJoint company — of which Reimer remains a shareholder — to scale the platform for broader use.

Expanding reach

More labs continue to adopt DataJoint to manage and refine their own data operations, with the company’s services and capabilities growing in response to demand.

“Labs must invest in their data operations to stay competitive, given the growing importance of AI and advanced data science techniques,” says Marshall Hussain Shuler, a neuroscientist at Johns Hopkins University School of Medicine in Baltimore, Maryland. His lab gathers mounds of microscopy and video imaging to study the electrophysiology and behavior of neural circuits in mice, aiming to understand how experiences shape sensory processing and decision-making. “Making a platform that does all that in one place is a tall order,” he says. DataJoint helps keep it all organized and interpretable, creating what he calls a “lab memory.”

“We have already saved months and ran experiments that we never could before,” Hussain Shuler says. “DataJoint allows us to create a formal structure for our work that can be understood, extended, and reused.”

That structure is helping the team behind Project Aeon, led by the Sainsbury Wellcome Centre at University College London, to better understand the neural basis of natural mouse behaviors, such as foraging, escaping, nesting, and social interactions over naturalistic timescales. The project involves continuous monitoring of mice in large habitats, via dozens of cameras and sensors, for weeks to months, producing high-dimensional quantifications of their behavioural repertoire including pose, position, and identity. “Such in-depth description of mouse behavior, combined with prolonged ephys recordings from implanted arrays, generates very large and complex datasets that are hard to handle,” says Dario Campagner, Project Lead Scientist. “DataJoint architecture enables fast and intuitive data querying and provides an easy way to standardize the data format for sharing with researchers all over the world.”

Scrutinizing all that data is a task well suited for AI — and these tools require that every detail of an experiment be structured and recorded in order to generate meaningful insights. Yatsenko says the latest advances in the DataJoint platform unify an experiment’s design, code, and data — creating a structure that both humans and AI systems can interpret. “For labs seeking rigor, reproducibility, and AI readiness, DataJoint offers a path to organize complexity and unlock the full value of their data.”

‍

‍

‍Schedule a Call with the DataJoint Team:
Ready to transform your lab’s productivity, reproducibility, and impact? Schedule a conversation with our team to discover how DataJoint can accelerate your research and give your lab a competitive edge.

‍

‍

Sometimes a new computational paradigm can be better defined by abolishing distracting capabilities than by adding new capabilities. For example, Edsger Dijkstra paved the way for structured programming by arguing for abolishing the use of GOTO statements in programming languages (see his 1968 letter “Go To Statement Considered Harmful”). The constructs for structured programming had already existed but the paradigm shift needed the extra push by stamping out the anti-patterns enabled by GOTOs.  Today’s high-level programming languages lack the very concept of GOTO.  

DataJoint is a full-featured relational database programming language; it replaces SQL for defining and querying structured data. DataJoint implements a new form of the relational data model that is more semantically refined to make schema design clearer while retaining the full capabilities and rigor of the relational data model. It makes good design practices more obvious and bad design choices harder or impossible.

In this story, let me illustrate five of DataJoint’s clarifications that make database work more rigorous and teachable.

Clarification 1: Primary key not optional

SQL sees no problem defining a table with no primary key thereby failing to enforce the foundational premise of the relational model that all tables must represent sets (unordered collections of unique elements). DataJoint’s syntax makes it impossible to not define a primary key.  For example, the table definition below is perfectly legal in SQL.

CREATE TABLE person (
 person_id int,
 first_name varchar(30),
 last_name varchar(30))


A better design would define the primary key and make the required columns not null, resulting in this longer code:

CREATE TABLE person (
 person_id int NOT NULL,
 first_name varchar(30) NOT NULL,
 last_name varchar(30) NOT NULL,
 PRIMARY KEY (person_id))


The same definition in DataJoint would appear as follows. We use Python as the host language in all examples.

@schema
class Person(dj.Manual):
   definition = """
   person_id : int
   ---
   first_name : varchar(30)
   last_name : varchar(30)
   """


The divider — separates the primary key fields above from the secondary attributes below. There is no option to omit defining a primary key. All attributes are required by default and are not nullable. SQL made a terrible choice to make fields nullable by default.

Clarification 2: Missing values, defaults, and nulls.

The second clarification concerns the use of optional attributes. A field (or attribute) is optional if specifying its value is not required when inserting new data. In SQL, this is possible when either a default value is provided or the field is nullable. NULL is not just a special value; its logic is handled quite differently from all other values. A point of confusion is that in SQL a field can be nulllable and it may have a non-null default value.  

For example, in the following definition the field quantity is both nullable and has a default value of 100.

CREATE TABLE order_item (
 order_id int NOT NULL,
 item_id int NOT NULL,
 quantity int DEFAULT 100
 PRIMARY_KEY (order_id, item_id),
 FOREIGN KEY (order_id) REFERENCES order (order_id),
 FOREIGN KEY (item_id) REFERENCES item (item_id))


Then an  INSERT omit a value would result in the use of the default value of 100. The client would also have the option to insert NULL to indicate the missing value. This creates many confusing choices and behaviors. While this abundance of choices may find its use, more often than not, it multiplies confusion as we must juggle multiple definitions of the term “missing value.”

In DataJoint, one makes a field nullable by setting its default to null.  This sidesteps many problems of definitions and use of missing values.

The behavior is simple: if a value is missing, then the default is used. If the default is null, then the value is nullable. You can use either of two definitions:

# Definition 1
@schema
class OrderItem(dj.Manual):
   definition = """
   -> Order
   -> Item
   ---
   quantity = 100 : int
   """


or

# Definition 2
@schema
class OrderItem(dj.Manual):
   definition = """
   -> Order
   -> Item
   ---
   quantity = null : int
   """


In the first definition, quantity is not nullable and defaults to 100; in the second, it is nullable and it defaults to null. We give up the flexibility to provide a non-null default to a nullable field but it’s a sacrifice well worth the clarify of definitions, communication, and insert behavior.

As an aside, in general, the number of nullable attributes should be small in well-designed schemas. If many attributes are nullable, this probably means that they are defined in the wrong table and an extra table must be defined to represent entities for which these attributes are required and therefore not nullable.

Clarification 3: Foreign keys reference the primary key

Nearly always, the foreign key must reference the primary key attributes of the parent table and the foreign key attributes in the child table must have the same types as the primary key attributes in the parent table, listed in the same order in the foreign key declaration. That’s quite a bit of complexity to track. Yet SQL syntax makes no effort to simplify this most common use. The syntax for an invalid foreign key referencing a secondary non-unique attribute is indistinguishable from a well-formed foreign key. Changing the primary key in the primary key requires concomitant changes of all the foreign key attributes in all the referencing child tables.

DataJoint abolishes this complexity by requiring the foreign key to always reference the primary key and automatically introduces required foreign key attributes with correctly matching definitions.  Let’s define the table FilledItem referencing the OrderItem table defined above.

SQL first:

CREATE TABLE filled_item (
 order_id int NOT NULL,
 item_id int NOT NULL,
 fill_date  date NOT NULL,
 PRIMARY KEY (order_id, item_id),
 FOREIGN KEY (order_id, item_id) REFERENCES order_item(order_id, item_id)
)


The equivalent definition in DataJoint is

@schema
class FilledItem(dj.Manual):
   definition = """
   -> OrderItem
   ---
   fill_date : date
   """


DataJoint removes the flexibility to define foreign keys referencing anything other than the primary key but greatly simplifies both the syntax, shortens the learning process, and reduces opportunities for error.

Clarification 4: No cyclic dependencies

One of the most significant simplifications in DataJoint is the elimination of cyclic dependencies in database schemas. All database schemas designed with DataJoint must constitute Directed Acyclic Graphs (DAGs).

To illustrate, let’s consider the Sakila database for a DVD rental shop provided in the MySQL documentation and used by many tutorials.

Here is its Enhanced Entity Relationship Diagram:

The schema has a cyclic dependency. Can you spot it?

After setting up the database, we can connect to it with DataJoint and interact with its individual tables.

However, when attempting to plot the Diagram, DataJoint raises the error indicating that it cannot handle cyclic dependencies.

The cycle is introduced between the staff and store tables where each staff references the store and each store references its manager from the staff.

To break the cycle, we drop the manager foreign key and plot the diagram.

In Jupyter, hovering over the tables in the diagram will show their detailed definitions in DataJoint declaration notation.

Compare the EER diagram above and the DataJoint diagram. What stands out? Which is more amenable for rapid comprehension?

DataJoint schema designs have a direction. To populate the data, you start from the top of the diagram and move down step by step. In this way, the database schema also expresses the workflow for data collection and analysis. Having worked with hundreds of DataJoint schemas for 13 years, it now pains me to have to figure out a non-directional schema as shown in the conventional EER above. Databases need a sense of direction to help orient their users and designers.

But did we lose any representational power? Is there a way to represent managers for stores referencing the staff? It turns out that any cyclic dependency can be replaced with an equivalent acyclic design, which may require an additional to represent the relationship.

Thus we add the table StoreManager to the schema:

@schema
class StoreManager(dj.Manual):
   definition = """
   -> Store
   ---
   -> Staff
   """


And the diagram beccomes

The updated schema has an extra table to represent the store managers without a cyclic dependency, enforcing the same constraints as the original schema.

Clarification 5. Rows are immutable: no UPDATE (normally)

In the original cyclic schema for the Sakila database, it was not possible to populate the Store and Staff tables with INSERTs alone when foreign keys are enforced.  To populate Staff, one must have a Store and vice versa. Therefore, it would be necessary to make one of the foreign keys nullable, populate the table with NULL for the reference. After the other table is populated, then you would need to use UPDATE queries to fix the reference.

For example, we could first populate the Store table putting NULL for the manager references.  Then we would populate the Staff table and then go back and use UPDATE to set the managers for the Store.

This problem does not exist for the acyclic design. One first populates the Store table, then the Staff referencing the Store, and finally populating the StoreManager table referencing both.

Therefore, the final key simplification of the DataJoint model is the assumption that tuples (or rows) in tables are immutable.  They can be inserted or deleted but not updated.

The UPDATE operation is not used in normal operation. It still exists but it is understood that UPDATE is used for deliberate corrective operations, to fix mistakes but not as a normal operation in the regular workflow.

The assumption that tuples are immutable serves as the foundation for the data dependency model since foreign keys express dependencies between tuples (or entities) in the tables not simply between individual fields as is often assumed in SQL.

Summary

Sometimes less is more. By bad choices  less available available, DataJoint brings focus, increases productivity, and improves communication. The result is a modern take on the relational model for the next generation of scientific computing projects.