samedi 18 avril 2020

Design shared product catalog for multi-tenant system

I am building a web application for pubs and breweries to manage their tap list and display it on different media (TV screens in house, print, website etc).

Background

In the POC stage I went with a very simple system of Beer -> Tap <- Pub. Users simply select the beer they have on tap at a certain pub, enter their price etc and done.

They can simply add or modify beers (and breweries) and that information gets displayed directly for their taps, e.g. name and logo of the beer and brewery.

Of course this doesn't work well when many users start sharing this master data set of beers. You don't want user A change a name of a brewery and it affects all of user B's real-time TV dashboards.

Considerations

I'm currently exploring better options:

Option A - store beer and brewery information in the tap

This way there is still one master data set, but users don't need to edit it. If they want to change anything they can overwrite the default in the specific tap for a specific pub (either we copy all data at the time of creation or we only store overwritten attributes)

+ easy to implement, easy to reason about
- not very flexible (if they want to change the same beer across multiple locations or the brewery name across multiple beers) 

DB Schema would look something like this:

BREWERY
- id
- name
- etc

BEER
- id
- brewery_id (FK)
- name
- etc

PUB
- id
- name
- etc

TAP
- id
- beer_id (FK)
- brewery_id (FK)
- price
- etc
- beer_name
- brewery_name
- etc

In this case users wouldn't be able to modify existing beers/breweries, only overwrite things on the tap level.

Option B - create shadow copies of each beer and brewery for each user

This way there is still a master data set for all users, but once they select something from it to add it to their tap list, the system will create their own version (shadow copy) of it. They can then modify it to their hearts content and not affect other users copies or the source version of it.

+ cleaner design
+ allows updating your own copy of a beer/brewery and change affects all *your* data
- more effort

DB schema would look something like this:

BREWERY
- id
- source_brewery_id (FK) - would be NULL for source brewery (master data)
- user_id (FK) - would be NULL for source brewery (master data)
- name
- etc

BEER
- id
- source_beer_id (FK)
- user_id (FK)
- name
- etc

PUB
- id
- name
- etc

TAP
- id
- beer_id (FK)
- brewery_id (FK)
- price
- etc

This way users can edit the items they own.

One tricky part might be de-duplication when browsing and searching beers and breweries, but I think using the source_id concept I should be able to do this in SQL fairly easy.

Summary

I'm leaning toward option B but I was wondering if I'm missing something or if this is the right way to build a shared catalog.

BTW I'm building this using Ruby on Rails if that helps anyone making even more informed suggestions.

Thank you for trying to help me with this!

Aucun commentaire:

Enregistrer un commentaire