Sharing Many-to-Many under a single Root record?

[MrSkwiggs]

I'm trying to wrap my head around CKSharing, and while I understand the reasoning for not supporting many-to-many as explained in the docs, I'm wondering why I could not simply make all components of a M2M relation "nested" under the root record?

If we take the Reminder example, why could I not tie Tags to ReminderList? then it would be very clear that a set of tags only exists for a given list, right? Meaning it'd be clear what happens if a tag is deleted/edited (it would only affect the Reminders nested in that list, which are shared themselves).

I feel like this being such a clear-cut use case, it doesn't make sense that it would not be supported at the CloudKit level even... 🤔

[mbrandonw]

Hi @MrSkwiggs, hierarchical sharing in iCloud is modeled via a single parent association. If recordB has its parent field set to recordA, then when recordA is shared recordB will be shared too. But this simplistic model breaks down if a record has two or more parents.

Many-to-many associations cannot be modeled as a single parent relationship. They have two parents. In your example, I'm not sure how associating a tag with a RemindersList helps because the real problem is that ReminderTag has two foreign keys: reminderID and tagID. Further, is associating tags with lists the user experience you actually want? Won't people find it confusing that they can't use tags that were defined in one list in a different list?

There are ways around this if you want to explore them, we just don't have any of it detailed in the docs because each has their pros and cons. The simplest thing you could do is simply not designate one of the relationships as a foreign key in the SQL schema:

@Table struct ReminderTag {
  let reminderID: Reminder.ID
  let tagID: Tag.ID
}

/*
CREATE TABLE "reminderTags"(
  "reminderID" TEXT NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE,
  "tagID" TEXT NOT NULL
) STRICT 
*/

SQLiteData uses this schema description in order to figure out how to set up parent relationships in CKRecords. Since "reminderTags" now only has one foreign key, that will determine the parent. However, this does mean that data consistency will not be enforced with the "tagID" foreign key, and if you delete a tag it will not cascade to deleting associated "reminderTags". But, you can recreate those things yourself with triggers if you want.

And second, if you really do want to go the route of associating tags with lists, then you could use an set of IDs to associate any number of tags with a reminder:

@Table struct Tag {
  let id: UUID
  var title = ""
  let remindersListID: RemidnersList.ID
}

@Table struct Reminder {
  let id: UUID
  var title = ""
  var remindersListID: RemindersList.ID
  @Column(as: Set<UUID>.JSONRepresentation.self)
  var tagIDs: Set<Tag.ID> = []
}

This will mean you have to do more work in SQL queries and in your app logic to deal with this set of IDs, but it's possible.

[MrSkwiggs]

Thanks for the detailed answer, and the alternatives offered!

Let me preface what follows with: I do understand that the limitations are more deeply nested at the CloudKit layer, which really irks me. I was just hoping to hear if maybe there was a separate angle that would allow for M2M relations to still be shareable, somehow, without sacrificing default SQLite safety.

With that said, here's some context: in my app (which isn't a reminder clone 😄), the UX revolves around having only a single root record "active" at any given time. The schema goes like this:

• Household
  • Categories
  • Members
  • Accounts
    • Lines

Where a Line can have a Category, so I would need a LineCategory table.

But Categories are tied to a particular household. A user can have multiple households, but from the UX's perspective, they can only have 1 household open at any given time, so it would not make sense for them to see categories from other households when they create lines.

I think given the constraints, I'll go with the single-foreign-key LineCategory approach. Thanks again for the guidance 🙏

[mbrandonw]

Ok yeah, that makes sense to me. And there are others in the community that have decided to go that route to have many-to-many associations and I so far have not heard any problems with it.