· Oct 24, 2018

Best Practice for data structure for multi-user systems


I would like to know if anyone has had some experience in building systems for multiple end-users. 

To keep things simple, in a hypothetical example, say an Event Booking System, where a Venue could sign up to use such a system and define it's Venue, costs, calendars, etc.. and then invite their customers to book the Venue on different days/times. 

If I wish to offer such a system to many Venues, there are a number of options available.

  • Simply create a unique copy of the system for each Venue, in their own Namespace, unique classes and applications
  • Set it up, so that every Venue shares the same set of applications (saving to maintain multiple copies), but create unique classes for each Venue
  • Just provide one set of classes and applications and simply pre-fix every piece of data with a unique identifier for each Venue. So all the Bookings for example, would be in one large class, but each entry would be tagged with the Venue ID and the code would simply track all the data with the Venue's ID before creating or opening the records for that Venue.

From a maintenance point of view, only one set of classes and applications would make sense, but I'm hesitant in launching into this in fear of getting the data mixed up and allow one Venue to operate on another Venue's data.

So, my interest is in learning from other developer's experiences, if someone has gone down this road, what they chose to implement, how did it go, was the concept good down the road for expansion and would they do things differently, if they had to do it again.

I'm inspired by the likes of Xero Accounting, where you simply complete an application form, log in and immediately, you have a complete system at your disposal. Just by logging in, there does not seem to be a need for building your copy of a database and they manage it, as if you were running your own copy of the application. Seemingly simple. If you just wish to test the software, then close the account, no apparent need for any hands-on intervention form a human. That is the simplicity I would like to achieve.

Anyway, looking forward to some great suggestions.



Discussion (6)3
Log in or sign up to continue

Very interesting question. Actually, it mostly depends on your application and how do you work with private data there. 

You can make application for any number of customers working in one database, with good security which does not allow to access data from one customer by another.  But in this case mostly means, that your application should be designed so, from the beginning.

Another and maybe simplest way is making separate databases for each customer. In Caché you can create multiple namespaces with different databases for each customer, but with the same database for a code of your application. So, you can update all sites at the same time.

Nowadays, I would recommend looking at containerization of application. And with kubernetes you can very fast deploy any new site with completely separate code and data between customers. But it means some work on how to prepare your application to be deployable with kubernetes.


Thanks for your response.

However, your reply raises more questions than it answers.

I didn't know about Kubernetes and having a brief scan through the info, it sounds like a lot of complexity to add to the present Cache installation, doesn't really tell me how Cache and Kubernetes function together and looks like I would end up with a separate entity of code and data for each customer, which is what I'm looking to avoid.

What makes most sense is your comment on working within one database is .. "your application should be designed so, from the beginning.". This is exactly what I'm looking for. Design concepts for the scenario. Are there any Cache documents on this aspect? Any samples?



I'm not sure there's a Cache specific document for designs like this. There are multiple ways to separate data in any database (schema, instance, namespaces, etc). Cache provides extra features such as namespace mappings that makes sharing simpler but before you get to Cache specific implementation you need a high level design of your application. Regardless of database and programming language, how should my application work?  How do you plan to sell your product? How flexible you want it to be? As Dmitry pointed out, are there any regulations/limitations around sharing data within customers? All this should be based on your  business model and has nothing to do with technology stack. 


Your third option is the most generic and seems to be followed if you are developing from scratch because it keeps the door open for future extensions. E.g., imagine that a venue aggregator which provides on-line event search / ticket selling service would like to work with you. If you deploy your app as a separated instance or database for each venue, you would implement multi-database search for aggregating the events. 

Such technologies as containerization can help to organize the development/testing/[deployment(?)] better, but IMHO should not be of great impact on the app/database design. "The cart should not run in front of the horse", as old Russian proverb says. 

All approaches you are considering can work. if you're storing everything in one database, I'd recommend RLS.

Much more important question is do you need storing everything separately or in the same place.

Advantages of separated databases/servers:

  • Easy to scale
  • Would work faster on a lot of cases
  • Easy to delete/rebuild a chunk
  • Easier security

Advantages of unified database/server:

  • Cross-chunk queries are easier (For example: This venue is sold out on the dates you need. We recommend the following nearby venues)
  • Simple Backup/HA strategies