Best way to model the equivalent of join tables or cross reference tables in Datomic

Datomic Applications
1

We’re migrating a legacy Oracle Database application to Datomic and we’re trying to preserve as much of our data model as makes sense while updating to something that fits more with Datomic. I’m puzzling over what to do with a particular join table, which exists for two reasons: 1) there’s a many-to-many relationship; 2) there are attributes associated with the relationship. There’s a third interesting twist that’s throwing me off… the attributes associated with the relationship are specific to the current user. This brings in a third table in Oracle.

I have no trouble thinking of ways to model this in Datomic. The trouble is, what’s the “best way” or rather, what are the pros and cons down the road. Since I’m relatively new to Datomic, I’m having a hard time seeing how my decision now will impact us in a year or two.

Here’s the problem laid out more precisely. Say I have entities with attributes grouped under the namespaces :foo and :bar. These two sets of entities are related and the relationship carries a set of attributes, too. Those set of attributes are specific to the current user. The current user is defined by an entity with attributes namespaced with :user.

Here are some ways to model the attributes on the above relation.

  1. For every combination of :foo, :bar, and :user, create an entity and add attributes to that entity. The schema for this entity might look like:

    {:db/ident :foo-bar-user/foo
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Foo for this relation”}
    {:db/ident :foo-bar-user/bar
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Bar for this relation”}
    {:db/ident :foo-bar-user/user
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The User for this relation”}
    {:db/ident :foo-bar-user/an-attribute
    :db/valueType :db.type/string
    :db/cardinality :db.cardinality/one
    :db/doc “Some attribute on the relation”}

  2. In our scenario, bar actually logically belongs to foo and can belong to many foos. In the same sense that an instance of a Toyota Camry can belong to Joe, and another to Jane, Wanda, Howard, etc… And foo can have many bars, so it kind of makes sense to have a collection of bars as part of foo… so it’s also possible to have a collection of references as part of foo. But we still need :foo, :bar, and :user entities to identify the relationship attributes. I’ll say more on this later, but here is the schema:

    {:db/ident :foo/bars
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/many
    :db/doc “The Bars in this Foo”}
    {:db/ident :foo-bar-user/foo
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Foo for this relation”}
    {:db/ident :foo-bar-user/bar
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Bar for this relation”}
    {:db/ident :foo-bar-user/user
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The User for this relation”}
    {:db/ident :foo-bar-user/an-attribute
    :db/valueType :db.type/string
    :db/cardinality :db.cardinality/one
    :db/doc “Some attribute on the relation”}

  3. The last option I’ll mention here (though not the last possible schema to solve the problem) is to have an entity to model the relationship between :foo and :bar and then another entity to hold the attributes specific to the user and have it ref the :foo-bar entity. Like this:

    {:db/ident :foo-bar/foo
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Foo containing this Bar.”}
    {:db/ident :foo-bar/bar
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Bar in this Foo”}
    {:db/ident :foo-bar-user/foo-bar
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The Foo-Bar relationship”}
    {:db/ident :foo-bar-user/user
    :db/valueType :db.type/ref
    :db/cardinality :db.cardinality/one
    :db/doc “The User owning these attributes”}
    {:db/ident :foo-bar-user/an-attribute
    :db/valueType :db.type/string
    :db/cardinality :db.cardinality/one
    :db/doc “Some attribute on the relation”}

When a new user comes along and looks at a particular Foo for the first time, a set of attributes may be created for each specific Bar in that Foo. These Foo-Bars can exist independent of users, too. So the first scenario is lacking a bit because even though the foo-bar-user relation can have attributes, there is no way for the foo-bar relationship to exist without the user. I know Datomic doesn’t need the user attribute to exist on those entities, but I want my human eyes to look at the schema and have it make sense.

I kind of like the second approach because when there are no user attributes, the bars exist as a collection within foo. This seems clean. When a new user comes along, that collection can be used to seed the foo-bar-user relation for the new user. But then, what happens when something is removed from that collection… there’s no connection between the collection and the entities in foo-bar-user. This is what leads me to scenario three, which looks a lot like our current Oracle database. There seems to be more data integrity in scenario three, but I worry about complexity and performance of queries.

Any thoughts? Other schemas I should be considering more closely? I appreciate any help.

(I apologize for the lack of formatting in the schema code. I used 4 spaces and the editor did not format it.)