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.

  • Why do products have to be removed from the database in solution 2 when a list gets shared with a user that already has a similar product in another list? Also, what would happen if A and C both have a separate list containing "bread" and they both share their list with B? Commented Feb 11, 2016 at 7:28
  • @BartvanIngenSchenau because the product has to be unique in the app, based on name. Its lifecycle is not coupled with the list and it's referenced from different parts of the app. If I create multiple products with the same name sometimes one product would be referenced another the other, they sometimes have to be counted together, or edited. So this is not a good idea.
    – User
    Commented Feb 11, 2016 at 9:30
  • Second question: that's a problem I also thought about - this basically means that A, B and C end all sharing the same product, even if there's no direct link between A and C. So A shares list with B -> B now references A's product. C shares list with B -> B and A reference now C's product. C's product doesn't really have a meaning though, they just all share the same product.
    – User
    Commented Feb 11, 2016 at 9:36
  • With approach 1, each would keep their own products, but from the perspective of the user it works the same, e.g. A updates the price of bread, this causes the other user in the list (B) to also update it. And since B is sharing another list with C which contains this product, this means C's product also has to be updated.
    – User
    Commented Feb 11, 2016 at 9:40

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

  • Thanks for you answer! Well the approach you describe is exactly my point 2. (I didn't write about the n:n tables, but it's of course a part of it). The difficulty you mention is the reason why I'm talking about replacing. And this replacing will happen very frequently as the users will likely name products similarly (e.g. "bread") - the app even comes with a prefilled database (first only in the client - the app can be used offline also). And I also have then to do the reference replacement in the clients, etc.
    – User
    Commented Feb 11, 2016 at 14:28
  • And I'm afraid that with all this server and client reference replacement something may go wrong so 1. where each user has their totally separate items, and update each other via uniques (name, etc) feels a bit safer.
    – User
    Commented Feb 11, 2016 at 14:30
  • Completing the first comment, in case it's relevant, products are not global but each user has their own products. So I'd need for approach 2. an n:n table between users and products also. But the mess (or what I consider one) with the references is still the same... Problem is also that as I said the user can use the app offline, and when coming online with a lot of changes these reference updates are tricky.
    – User
    Commented Feb 11, 2016 at 14:50
  • ...and (I could go on and on, sorry ;) I actually thought about a solution for the sync issue, which is starting the sharing process only after sync, that is, I'd store an invitation to share in the database and after the user comes online and finishes sync they can accept the list share, which would trigger the reference updates. Now I'd know at least that the user's local db is up to date. But I'm still sceptical of the reference updates. Feels somehow like my users will end with a lot of "unknown error" dialogs...
    – User
    Commented Feb 11, 2016 at 14:57
  • ...but on the other side having all these copies in the db and having to update them always at the same time feels a bit stupid stupid so I really don't know what to do.
    – User
    Commented Feb 11, 2016 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.