I have been thinking about this a few days already, doing diagrams, started programming, reverted, started again, still can't decide which is the best approach.
The problem is about sharing groups of items with other users, where they can get/add/edit/remove, in real time.
My items specifically are "list items" which reference "products" which reference "product categories". All of these entities use uuids. So a list item has a uuid, a product has a uuid, a product category has a uuid. In the database they use foreign keys reference the dependencies via uuids.
So to share a list, which is a group of list items, with other users, I narrowed down the possible solutions to these 2:
Replicate everything for each user. This way each user has their own list items, products, and product categories. The list items get a n:n relationship with users, and I of course filter the list items for the user requesting them. Add/update/delete has to be done always for the copies of all the users, in a transaction. So for example user A has "bread" with uuid "1". Shares list with user B, now user B has also "bread" but with uuid "2". If I queried the list without filtering by user, it would contain 2 items. "1"-"bread" and "2"-"bread". Problems here: 1. Redundancy/space in database. 2. Need to use semantic unique a lot to identify the items that belong together (a group of copies).
Make users reference the same list item and the same dependencies. This means that when a list is shared I have to check if the other users have a product or category with the same name (they must not end with multiple products with the same name after someone shares lists with them), replace it with the product or category of the sharing user, and make all possible list items and other types of items they have that could be referencing this product or category to reference now the product of the sharing user. When a user stops sharing the list with another user, then I have to go through all the elements from the removed user and create their own copies and again make also that everything pointing to them points now to the new copies. Bread example: user A has "1"-"bread", user B has "2"-"bread", A shares list with B, this makes B to remove "2"-"bread". All the items which B may have that point to "2"-"bread" now have to be updated to point to "1"-"bread". If A removed B from the list, "bread" is copied for B with a random new uuid and all of B's references to "1"-"bread" have to be updated to point to this new copy.
Additionally to both points it comes, that I'm using an app with its own database as a client which needs to be synced with the server (it basically mirrors the structure of the server, same uuids etc, of course the app holds the state of only 1 user).
I'm omitting many little details, like question of what should happen when a user updates a product that is not in a list (which is possible) etc. but I think this is not the core of the issue and want to keep the question simple.
Right now I'm favouring 1. 2 Has a cleaner structure in the database, and it makes sense semantically as it represent what is happening in reality - multiple users are sharing the items (and their dependencies).
But 2 makes me cringe a bit with all that replacing-and-updating-references, specially since I have to do this again in the client, and this is not done only when a list is shared or a user removed from it but also each time when a user adds a list item to the list (have to check - do the other users have product(etc) with same name? if yes, remove it, update references), and since the app is usable also offline, and when it comes online after a while it needs to do a "big sync" in which case maybe other users replaced some references in the server in the meantime and and and...
1 feels safer. I still need to write a lot of code and transactions to ensure that everything is updated consistently but I also have to do that with 2. If something unexpected happens I think it is less likely to mess up things than with 2.
Any ideas about this? It's the first time I'm writing this kind of logic and any input is welcome.