Advice for Entity design decision

[zirkelc]

Hi,

I'm seeking advice on designing one or more entities. Let me explain my use case.

Imagine a multi-tenant smartphone scenario:

• A User belongs to a Tenant.
• A App belongs to a Tenant.
• A Homescreen belongs to a User.
  • A Homescreen contans multiple Widgets with a specific order in which they are rendered
  • A Widget is a reference to an App with a name and a size (small, medium, large).
  • An App can exists independently of being used as a Widget.

At the moment, I have identified three entities: User, App, and Homescreen. The Widget is not a separate entity but a custom array field within the Homescreen entity. Here's a simplified representation: Playground

In this setup, querying a specific User's Homescreen requires just one query. The Widgets on the Homescreen contain the necessary information for rendering (name, order, and size), with the order determined by the array index.

However, I face a problem when deleting an App: I must also delete all Widgets referencing that App from all Homescreens. To optimize this access pattern, I've considered treating Widget as its own entity with a secondary index for TenantID and AppID. This would make it straightforward to query and remove all Widgets tied to a particular App upon its deletion.

However, by having the Widgets as a separate entity, I lose the array index to order on the Homescreen. I've though about two approaches:

1. Keep the Homescreen with a Widgets fields that is simply an array of WidgetIDs. The array index is the order of the Widget on the Homescreen. That means I must query the Homescreen and all Widgets and render the Widgets according to the array indexes. However, when I delete an App, I must delete the Widgets entities and remove the WidgetIDs from the Homescreen entity.
2. Merge Homescreen and Widget into one Entity and add an order field to this Entity. However, re-arranging the position of a Widget means I must update all other Widgets as well. Even worse, when I add an App to the Homescreen to create a new Widget, I must query all other Widgets first to determine the last order value and increment it.

---

I hope it's not too difficult to understand what I want to achieve :)

[tywalch]

Does this sound like I understand the crux of the challenge?:

• Users use Apps as Widgets, but an App exists whether or not it is actually used as a Widget
• When an App is deleted, you need to ensure that you also clean up any references it had as a Widget
• Regardless of how things are modeled, it is critical that it allows the user to control the order in which Widgets are presented.

Is that the extent of your requirements, am I missing anything?

[zirkelc]

Yes, that's right!

[zirkelc]

Not to bug you, but have you had a chance to think about it?

I'm not satisfied with the alternative solutions I came up with yet. I am currently leaning towards handling the deletion of an App as a DynamoDB Stream. The stream handler would have to query all Homescreen for a Tenant and see if the App was used as a Widget. I could add appIds as a top-level list attribute and use a query with appIds contains($appId) to push the filtering on the server-side.

[tywalch]

No it's ok, here's some general thoughts:

In an ideal world you're able to leverage an index to find all relevant app usages. In this case that could be that you store app usage, separate from the app order. That could be as an individual record per usage, like a reference record between app and user that's presence signals usage.

In cases where you do have denormalize data, or write twice, determine: the actions/scenarios that interact with this data and rank them frequency they are encountered in real life. Their frequency is a good indicator of what should ultimately be "simple" to do and what is ok to be a bit more complex. In your case, that might rank out to be the following:

1. User gets widgets/widget order
2. User changes widget order
3. User adds new widget
4. user removes widget
5. app is removed

Additionally, you want to limit the degree to which your system relies on consistency between these records. Don't put yourself in situations where consistency is difficult to write/maintain, but on the other end also don't build solutions that require consistency to read/function.

Lastly, be sure to add some sort of versioning aspect to your records so that you can apply condition checks when performing complex operations. This can be a version number or a lastUpdatedAt timestamp. If you maintain the homescreen record you'll need to get the list on the record, remove the reference to the app, and write it back. This is an unsafe operation, it could be operating on stale data, unless add a condition statement that checks that version/lastUpdatedAt you're updating equals the version/lastUpdatedAt you retrieved.

If we took the approach of maintaining order in a "homescreen" record, and also maintaining reference usage records, your database operations remain pretty simple.

User gets widgets/widget order:

• Get homescreen record

User changes widget order:

• One change to homescreen (assumes the user is not able to "add" a widget in the same operation)

User adds widget:

• Check if app is "active"
• Write new app/user reference record
• Write homescreen record

User removes widget:

• Check if app is "active"
• Write new app/user reference record
• Write homescreen record

Remove app:

• Make app as "inactive"
• Crawl app usage records for usages
• Use identifiers on app usage records to also find associated homescreen records
• remove usages and homescreen references
• Remove app from platform

[zirkelc]

Thank you for this thorough answer, that helped a lot!

I tried to translate your ideas and use cases into a ElectroDB model and implemented the operations. Could you take a look at this Playground?

There are a few things I'm still not sure yet. I have used transaction write if a use case spans over multiple entities (user adds or removes widget; user removes app). Especially the last use case (user removes app) is a bit complicated. It seems transactions don't support batch delete and batch write, so I map() over widgets and homescreens and delete/update these in single operations which I spread into the transaction. Not sure if that's a good idea though.

I added a conditional check on homescreen.updatedAt for the use case user changes widget order, because I get() the homescreen and update() it on two decoupled operations. But, do I also need to check for stale-ness during the transactions?

[tywalch]

I threw this together this morning, I only used the playground so don't consider it tested or "complete" but it should get the point across. You want to ensure that your operations remain idempotent so the caller can retry when failures occur:

https://electrodb.fun/?#code/PQKgBAsg9gJgpgGzARwK5wE4Es4GcA0YuccYGeqCALgUQBYCG5YA7llXWAGbZwB2MXGBDAAUKKwBbAA5QMVMAG8wAUT5V2AT0IBlTADcsAYziEAwqlxUokgIJUq2AEaoqcACqbpp1eq0BJN0lzcgY3NQ0qTUC4YN9IzQAlOCM5GDAAX24MGzAAIkQUx1gnPIBucVS+KzBUVCx0gF4wAAoASjBGgD4wAHIGJyNeitEqmqoBhFJmvM0oVAwAfQmnKcW+Bkk4csqoaoVLTE6wPjgWeK0W0TAla5uwSVhEAC5b+-v+BNe8w4w8-Du730mFwWD23wAjP9AfdiBhDCZvhNcABrBjSaR5GEZAHvMKOLAuNy4V6KGE3NwbdT+GCk8n3KLeb5WbB8ADm0Pe73IaCw5FpYEc6FxXJx9N+NLpXIZXjgzIJ7M50rIcF5-NeQrg9LF0tQ0hgYTgMHsUuVjLl+T4qEkTkwSulLDCRjo3xA9q5PPq6sFGGF9Ju8C4DEoVFe7U6PQAIoaAHR8KAsdoi6XEUOtDrdMDRtxxhNJ7XS7HJsBYARwAAeeFN7ycmgAqnDJW9ldIUdXlVwcAgBXlW+7pakZFBQW5XgBtPKUhjUmB5AC6-syxfeqPb0s7iB7qP7XMHshHFonEtnC+VmW1xYydx1SkFk1IV7aI1Gexq6Okx1O5wilzuZPejzwAga5gJ8WjfO+O7AhgoLgvkULLkQBjGBak4MKikFFjC+LOK4VbNu8U4ziBFKyvKrIcohNyenyRoar6pgXvS75Nv+ZpkfkLKlpRi40d6mpMdKGxbCRgocXkXGKohN54kYGjAqJ5rfE4UBQFM047gGcBBiGrxBggxDSYheoGm4xppmx0pKZa1q2n8VGsE6Lr5G6Dl8XRPp+megbBtQYYZlGsbxombQOam-kRlmQV5qFBZclh7ylvAlYkgR9y1rYGKsYuraiTcG7dt8fYOTce7Duwh6Tvw05UDS86LjJXKrml65dluKKafcZUHuOeQsSeDWCfcV43DeygrFM55PuI5pgAA6g0bJwAozRsWwMBLbVAqSWyFQ3P1rw7XtgrsFMh0KrtdyggAXqhuCSAwCAIHkYAAD75FsMBYNaL3vXkCBMEtOxXi++xgHQNh4EY5D8J+ZwXFEVw3JZDxPMBLWgX4UTfBDWy4NDJB8DucIIqhyJohiUEgmCfCQli7yNThhJ4alKMUtVxEY6RTKcRdnXuQKmpGeKjYCmzMo8xJfNuaqXoeULQ3vOtm2s4u1kWFYNj2ASRIeLKAA8C0bctY5zl0LR9Xwmh5LFZ4C-RXmisZ+qGuZiniVaNp2g5jpUM6rr87LtGCwxDk+bp6aRdmcC5iFYXLRFmbR7H+bSlk8UM8WSUVvh4u1g2mDZWeuVc11Nj7hVvVEVt84ldwbVFR1DVhW2peleX5WjmAR6i7Xi75Q3nFN2eI1O9i14iuN95TSMYwKO+DYMEtcPfljmhI2lgEvBjYHY-kC+4Ev2yISTKFIuhFOYoh0GwbT8H08NxZM7rqtWRzW3u5LO2B2q8uh4r9xlbLSLuxL+0teJB34v-NOxlRafzuuAu2kC-6O0zsxLKYs1biW-jLX+IdUGP3FC7MyJpS7WTyJ7OynVfb+xcj-OW+DGLeW0r5NM4Yk7RTjoucKkcOE5mCqnUUCV7jZxSiBWsRtNogOlCXcW7xuqV27lVKkNdCA-F7nOOuBUex9mbtw1uciy5Dh6ko-q-x8hAJrpo-u9dNzMmHsqUeaDpT5zgaXUR3w2SggAEzmOVDlAxNiFFdwnNXOq1izwDzsfkLxWBvG6JHi3PKYBgmVWPH3SJtjCoxJ8duQaMD6QZQwaJDxOSsAIQCck1JVd37hK0YPPIsSIQJMcUktuKSO4mInGYiJkTtGeNBBCPJI8AGjzGneVYD5RDTVBjUU+JgV5gD0PCFCG8Ua-CfhiYsuMoYwz4Js6Qi8loinGRNB8AIZmzQABKQ3xnsyRy1jg-iiMkVIGAYD63NFALg4NbkE34F0McvRLG4F6HOMcAAGOcIxZoqHLNIac8AYA3Lxv8vgDyVpgDuAAMjACi3ZhMMU4oRtEIInzZTfLAO+LoIxQBYuELUOEYAoDeGqL81Fez6UiFEMAYAYAAC0PRNrsoJbDWsjLC7pCxbNAA4stfFdzCYAHlpAaFfMcNiYTtoXWOsec6FEKgg3QpoPgRhuCoFNWqvgYBNoKrRRi3ALQWVWtSnKqgdq9kqpdW0V4AAFHIkgsDEH1nChFZZkV-PuYtE2Ztmxz1vFqwgx5MjHGdTTXAx143KFMgwFNzQGCOnYEhFZJgYy7xwLgGMOzFX8BhDGTaLRxq1JgEm0WmQ2h1rZFAdoIx25gxBa8UNiKjQesJdGqgptjim2OncLAPyWgAEIc0dBRuQKgCxrUguOiDfahaFD+psEGmOj0EAtBzTGEFMYHrSBaMa01rRLEBTSlmqlGC82sHHcdPtNRs1hFzVkfNe7i2kzLVjCtMZ3z1uWo2190gaSEC1e2+t3bprvBhLywUdBSCIGICk9CpAg1UqIFgNk1rcCQw4NxZl1qOCkCMOpa1epiPwHBuhG1exSAsByOydDfKsAqkHFsAQYAEBQDZGyKjiK7woio+QVICxcMwjna0HNYBsW4vPQwOSWBgQrvpJe6Qlg6CNsXDGMzliHJmZjDm6SHaGbXjaDM6iy0N0fuNjQQ14g6XXHAL8FJjB2R4Dc8KtImAfNiAw4KpCChq1ouC480LGBRCzT0O6yNyr3mYC9emjVdwtX6u4rqtxR07gDrxel-gGLp13ABlYOsxCjT2AAPyvEoZgTzPK+UPRRKQFlmAwg0xLPAWQlIFAsCw9a-coJJmiDvWargFrtN7Gi6O-gTrVXpteKl1bfAlWZYwNl18env0KCbSo+DEqMAXZBYQWrVB6umUawoADzKNuvkza+U7YBVMvYLQwItsW9l1r1HCKgMHE2XZpO2utqYYMguh+8C9WHyAtBgyZV29gly3njFQOFQaaC3bcO2yK9I7sPYxytRozQLWBlLEaRcTWThQFx+WfHjr0ckKoHZ5UrxqBwBaBzp7t30L3Ya+ZbnNwJdgGQ428guBZDVAtP0J66wzi9AyKh+lKp10YGtTmjr3mGV+YYDAQQ8WqDheS7KMAthTc7YxYdtlq07h6qIDqvLzaCvsmOmTsXzXWu2Xa3cCs3g5K2G08CFrYAVJqTgNOY6B8j6vCVYGqg+t7fjsIMC8dNJeg0tEEa3AJr5uLatVSu3FX0XjvWy614tuI0crHe5x3uBjsdLBsoRPxzBTNtbZK4XdW-dUEICHoo4f5LTE8g+VNb3qgfY77Bi7N0fCREmr9jERy4Dz5qJYqH1P6gwB7XcNdrn5kx0cNOQ+S2+B1q4xVVHnetmwc34QQHhNieZjHPSSLPRnQpBRCWD8u+CWEIFphPugtIKZn-kYCiODr3ovukBrqZuNpgPzjBmATpucreKqB-j0KqLehHj4KPmHoQY5lATYKnoIu8D-ikqEETpYq0O+MAH5uQFwKgaanAFLvtBvofEtFAXQfzmdjVNdjni2pDmIf1AjsqDGIJuwFQfcDQYLiKjWnfO+PwOkLvjAPSG-rWmeDGCDpgGDkITOH3ldogVwfcBBhiOoXDuOqlGOI-nBmIcvghqdD4JoZkHOFIdKEjqgQ-rUEPljsoDjnjlYLQHzrgVrmeL7o9uZJ0FTrUGWJ2KcFoZkjcIziEazmEQLkPhYdKLzm4DkbEfYAPqLsUVzouHkdLrIWDrbPcHOFLjLtNCDIbr5kyuQI8MCEIJYpbrNMkJ0XABns3rPkIM7jcK7iVuzOdtqgaqVqIV7pdNwU4QsT7iLuTpzgHl7BgMdMQVQOPpgVHjHgxh1nNuapaoNh0VAMCEMQ2mmq+K8P0VcYMZXg7iMW3i+poQhvAceIQP1KUesULqBPCmPoQccJqO+ncXPncPGjodaoBv9goGfqBpEOBrCVBkYRDseBrk0cdMpoumiapq9O9ASX+voYET0DERTm3hSHQDkOcF+KoBgDkBgC0L0P4HwPoI9A0MoWir0Jrjuu3jvnYccCSRMBenYTGJ2HziyQwZmJYuKe5lDgugkZoU5trqfshKWhftUBgXsLftgIUegU-l3j4LCZEV-tKDQdAQAcpsAYRhgcCBAVAVhjAXAdMb8W+kgXoSgSjugaCTiNgcgJEfgQ6UQcCSQRPmQXoTUTBnLgrsQK8MrggIsGpDAL0O2ohFaQIebowRiMwe0dpOwSYBYSaaZkBMtIIT3u6ebhdn8eId4VyDIRQXIcoHGa+Erieimd2OmRrpmXylFkobCa8GocJpodoZXqZgYfIG6cIWIT8ebkIF6dIT6WgcoILnEQGcoBERrpFHzkURTv8bkVUU2ZIJQa2XgPGR2SrqmT2XUTcA0Z2ihp5q0ZdiqAMaARiFymIH0bEE8ZlNIC3rlksU2JMVWbOSsQXjNNbo8cCP+WYNOCYFMDACqv1mXs0LYHukaPrMkDrnwJ4N4OSt4JSpcdcS8eOl0ICr0DmmCiMKcQtucctiRXAP+TXptmADBcxRiC3j6mAAeoGsGhxXBQhYUMhd4BgANnsKbD0GxC+nWYhi9pCRmncHcH9kWkieWngFYZAYjgin7MZsYVtB6U4Q2TGLDp3oQXpI9LhkuTcDieICdguccKpYiZqTHBpbgHWiaTGGgJgJoHWkUk4TOSYQgSZV2jBgiktKlEmbeb2oKQoEYMJUhSheJbXuxb+bBRiPBRwUlWJRJXwJOs0JOsdFwHIK0PGgwZSpesus+p9reHJd8UPqYSIYqYgccJYl+lEWABhtYDAFAK8GMA0EcFxu+FRpRkIAlU9CWNamuhgJoCJmJsYDCC+gldlUaIQD9k5UBkxTcdBuLHWfSImiLP3vpqIYhJSRsQEeUYhLsfsQpNwFZUwsNGqTcHiStYhUaNSV1IlUaMlXlZWoZrgMZsuh1WMvZYAWVd9aJahemjGFMOyBwJ9SfrrnhqtVDSlemtumDc5cBihMiewOBu+HWuWYUQZbWZ6Y0WFWqUjdatVhkEAA

[zirkelc]

That's awesome! 👍 Thank you so much for taking the time to help me out!