Generic approach to sharing of items

Question

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:

1. 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).

2. 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.

Answer 1

There is an easier solution that doesn't involve a lot of duplication of your data or updating all kinds of references when a list gets shared or un-shared.

In your system, you have three relevant entities: Users, Lists and Products.
There are many-many relations between Users and Lists and between Lists and Products, meaning that each User can have multiple lists, which can also be shared among multiple Users. Also, each List contains many Products, which in turn can belong to multiple different Lists.
I am leaving Product Categories out of consideration for now. They don't really change the picture.

The key now is that these entities and their relations are all reflected in the database, so you should have a Users, a Lists and a Products table, as well as Users_Lists and Lists_Products link-tables.
When a user wants to share an existing list with another user, then that involves just adding a new row to the Users_Lists table and undoing it is just the reverse.

The only difficulty in this setup is if a user adds a new product to his local database and then syncs that with the server, while the server already has a product with the same name. In that case, the server will have to replace the new product with the existing product and update all references in the incoming data.