Database modeling

Hi, I think it would be very valuable to provide users with examples of database modeling for NoSQL like Appwrite.

It would be further appreciated to know whether data duplication is necessary to avoid listDocuments vs getDocument.

Eg:

Collection: Posts
postId: string
postContent: string
images?: string [] (imageIds?)

Collection: Images
imageId: string
imageUrl: Url
postId: string

So either we query all images for the post via listDocuments or we have an images key in each post that has the imageIds that the post has which would enable us to have getDocument but increases complexity by having data duplication/reciprocal design.

So my question is more of a request to have more info on what is recommended or what to consider when designing database schemas for appwrite.

Kindly

Is listDocuments as performant as getDocument? Then we don’t need to have the images key on each post. But if it’s a huge performance loss it’s a no brainer right?

What of many-to-many relationships? Googling will give more insight on what to do and Firebase database guides are almost applicable but they have enough differences that make those approaches not ideal for appwrite.

Thanks for taking the time to read and I’m looking forward to hear your thoughts.

Collection: Posts
postId / documentId
postContent: string

Collection: Images
imageId / documentId
imageUrl: Url
postId: string

I personally don't like using array in Appwrites current state, because you can't update a single item in the array. You gotta do a full update

And there can be a mismatch because of race conditions

I do not recommend using relationsships either lol

Not until they have improved it

In images collection you can put a key index on postId

getDocument is cached and listDocument isn't. with getDocument you always get max performance but if you set key index on postId you get the same performance as getDocument basically

Interesting, thanks for your insight. I have had the same experience as you unfortunately. Relationships have treated me poorly, stuck attributes, slow performance, related documents not having their relationships show up with the expected keys.

Backup your data 3 times for good measure before you are going to use relationsship lmao

Are the posts are going to be updated often? Can the user only attach images to a post when he creates the post or can he update the posts with new images later on?

Posts can be updated and probably will be often, they can attach images at creation and update or remove images after the fact.

Okay then I would advise to stick to my solution above

If the posts are rarely updated you can use arrays