Composite Keys on Entities

[drusellers]

Is it possible for aggregate (today) to manipulate tables with composite keys?

The model I'm trying to get aggregate to work with is

I'm trying to map the membership relationship.

Thank you for all of your help already with the documentation.

[friemen]

I'm trying to understand your question, but currently can't see a composite key. The picture shows a linktable 'memberships' connecting three tables. I assume this implements a m:n relation between organizations and users, where each connection also carries role info with it.

If you have freedom regarding the DB schema, perhaps you could include the role/name as column of a memberships record. Aggregate then allows you to specify the :query-fn and :update-links-fn for a ->mn relation that would take the role/name into account.

If you provide some info on the conceptual relations you're trying to implement, I'd be happy to take a deeper look and help with the correct er-config.

[drusellers]

@friemen thank you for taking the time to review my model and usage of aggregate.

So by composite key I'm meaning the memberships table as the PK for it is all three columns (organization_id, user_id, role_id). The goal is to eventually add more data to the membership such as time joined, etc. So the goal would be to see something like

{
  :id 1
  :name "Dru Sellers"
  :username "drusellers"
  :memberships [
    { :date_joined '2016-01-01'
      :role { :name "athlete" }
      :organization {
        :name "The Corp"
        :id 1
      }
    }
  ]
}

But to get there I need to specify, what I think of as, a composite key. My terminology may very well be off. It looks like with aggregate I could totally get there if I had a surrogate key on memberships. I do have total control of the schema but I thought I would ask if aggregate supported composite keys.

Pulling some thing out of thin air now:

(agg/entity :accounts.memberships {:pk-kw [:user_id :organization_id :role_id]})

I'm not clear how to best structure this to achieve my nested graph when my membership aggregate DOESN'T have a surrogate key. If I just need to get over it and add a surrogate key that is absolutely fine, :+1:

[friemen]

From your sample data and your goal (i.e. to have more columns in memberships table) I conclude that you can go with 3 relation definitions, for example:

(def er
  (agg/make-er-config
   (agg/entity :users
               (agg/->n :memberships :memberships))
   (agg/entity :memberships
               (agg/->1 :role :roles {:owned? false})
               (agg/->1 :organization :organizations {:owned? false}))
   (agg/entity :organizations
               ...)
   (agg/entity :roles
               ...)))

Translated for humans this is like:

• Each memberships record contains a foreign key to a user, implementing a 1:n relation from user to memberships.
• Each memberships record contains a foreign key to an organization and to a role, implementing a n:1 relation to a organization and role, respectively.

You're right that Aggregate requires a surrogate id column in memberships table. Would that solve your requirements?

[drusellers]

@friemen thank you, I have added a surrogate key to my memberships table, which was much easier. for purity i'd love to see composite keys, but not sure its worth the effort. :) :) thank you for all of your help.