Only entity-representing tables

In the traditional design process, the conversion from an E-R design to relational database design produces two types of tables: entity-representing and relationship-representing.  Simple 1:1 or N:1 relationships don’t need relationship-representing tables and require only a foreign key originating from the N side of an N:1 relationship. In contrast, M:N relationships, higher-order relationships (e.g. ternary L:M:N), or relationships with their own attributes require a dedicated relationship table.  Thus there are two types of relationships: those requiring a dedicated table and those that don’t (although both types require foreign keys).  Relationships with their own tables get their own name and can be referenced directly whereas the table-less relationships only get a foreign key and are identified by the entities that they link.

However, the distinction between entity-representing and relationship-representing tables is somewhat arbitrary.  We can always modify our E-R design to replace relationships that require tables with another kind of entity.  For example, synaptic connections between neurons can be thought of as a many-to-many relationship (See Figure below).  Alternatively, a new entity class, Synapse, could replace the relationship.   More abstractly, if we represent a graph of binary relationships, we could think of its edges as relationships or we could think of them as their own entities with their own relationships to the nodes.

I propose that, in the DataJoint model, we always redefine relationships that require their own tables as entities.  This purely semantic convention allows simplifying terminology and discussions.  We no longer need relationship-representing tables.  All tables represent entity classes and all relationships are expressed as foreign keys.  All relationships are directed and binary and their cardinality is always 1:1 or N:1.  If other kinds of relationships are necessary, they need to be redefined as entities.

Of course, users may think of some entities as relationships rather than entities.  For example, Synapse can be thought of as a many-to-many relationship between Neurons.  However, DataJoint’s notation and terminology will not distinguish relationship-representing tables as such.