Stritar's chronolog

Solving global and local records in SaaS database design

Sat, 20 Jul 2013 23:50:56 GMT

Lately, we've been seriously considering developing a new version of our framework. This one is already a few years old, and besides other problems it's facing, it lacks one very important thing - it's not suited for SaaS (Software as a Service) applications. Often, we would like to host a few simple projects (like multiple web pages) in a single database, but we are also thinking about developing a product / service, which we could offer to multiple clients. Making a product for different clients that would live in the same database is not simple, and requires an architecture that is both rigid and flexible, micro-useful and scalable.

The database model we currently use is doing quite well. It's able to support complex business-oriented systems, social portals and web sites, the core tables being the same across all applications. Sure, there are many redundant things, but we are aware where we should scale-down. More importantly, we have to decide how to upgrade this database model which is used in different applications to be able to work in a single database. I'm not that fond of using multiple databases or schemas in SaaS architecture, since this is a similar situation to which we have now, and it doesn't work that well. I'm certain having the same database for different clients is most suited for our business.

This means we will be having a ClientID (ApplicationID) in all the tables that will be used by all clients / applications. But what is the best way to take care of global and local records - records that are used by everyone versus those in only a few applications?

Let's assume we are building a CMS system for many sites, for which we will be needing two global Roles (Administrator, Moderator), but one instance will have its own Role as well (News editor). There are many ways we can go.

1. Local roles

This architecture means each client having it's own Roles. It's a simple solution, very flexible, but has many flaws - the most obvious being the benefits of global functionalities. E.g. if you're and Administrator, you can restart the site. Since we now have multiple Administrators (ID = 1,3,…), features like these get a bit harder to implement, and you can end up developing a totally different application for each client.

ApplicationID	RoleID (PK)	Role
1	1	Administrator
1	2	Moderator
2	3	Administrator
2	4	Moderator
2	5	News editor

2. Global Roles with a M:N table

The other option is to have global Roles, which are attached to a single application using a M:N table. This is an elegant and very flexible solution, but from my experience, you should avoid using M:N tables if possible. Of course, there are logical cases in which you can't, but you should always consider other options. Adding another table to the equation complicates queries, makes direct data browsing less understandable, besides raising the possibilities of errors.

RoleID (PK)	Role
1	Administrator
2	Moderator
3	News editor

ApplicationID (PK)	RoleID (PK)
1	1
1	2
2	1
2	2
2	3

3. Composite key

Another possibility that comes to mind is to use a composite key, which in reality, makes a single table from the above two (Roles, ApplicationsRoles). This allows using the same IDs for the same Roles in different systems. But it also requires having a composite key, which makes other things more complicated. It prevents making simple joins (you need to make a join by both primary keys), which means all the tables joined to Roles would need the ApplicationID field as well, even if they wouldn't really require it. Not to mention this model simply calls for trouble.

ApplicationID (PK)	RoleID (PK)	Role
1	1	Administrator
1	2	Moderator
2	1	Administrator
2	2	Moderator
2	3	News editor

4. Fake composite key

Sometimes, it makes sense using an additional primary key in a M:N relationship, so you can easily join the table further. We could do this as well, making the previous case a bit more easy-going. But this situation makes all the magic that we could do across the system a bit harder - since all the joins would be made using new local IDs, we are getting too close to the first solution (you can't make joins with the RoleID field, since it's not unique).

ID (PK)	ApplicationID	RoleID	Role
1	1	1	Administrator
2	1	2	Moderator
3	2	1	Administrator
4	2	2	Moderator
5	2	3	News editor

5. NULL-able ApplicationID

A different architecture enables using global Roles that have no ApplicationID, and local Roles that have it. Each application is set to use both sets of Roles, allowing flexibility and understandability. But this model has a problem as well - we can't prevent an application from seeing a global Role - those are intended for everybody, which means that in the long run, the system would start gravitating towards not having any global roles at all, to assure flexibility. To simplify - this architecture is great, but needs something that takes care of Denying.

ApplicationID	RoleID (PK)	Role
NULL	1	Administrator
NULL	2	Moderator
2	3	News editor

5. a) Creating a table for Deny

Let's put another application (ApplicationID = 3) into the equation, which, for some reason, doesn't want to have the global Role named Moderator (RoleID = 2). Adding another table to care of Denied Roles would solve the problem perfectly, but do we really need another table? We want to keep our database with as little tables as possible, so it's easier to use. That's why we should consider extending the Roles table to handle denying as well.

ApplicationID	RoleID (PK)	Role
NULL	1	Administrator
NULL	2	Moderator
2	3	News editor

ApplicationID (PK)	DeniedRoleID (PK)
3	2

5. b) Adding two fields for Deny

In the most normalized way, we need two fields to take care of Deny - a boolean to mark we are working with a Deny record type, and an integer to tell which record we are denying. But what if we want to optimize even further?

ApplicationID	RoleID (PK)	Role	IsDenied	DeniedRoleID
NULL	1	Administrator	false	NULL
NULL	2	Moderator	false	NULL
2	3	News editor	false	NULL
3	4	NULL	true	2

5. c) Using an existing field for Deny

We could put information about which record we are denying into the existing Roles.Role field, since we don't really need it in these cases. But there's a major setback; if a non-numeric field would be accidentally stored in the denied Role, the SQL engine would throw an error.

ApplicationID	RoleID (PK)	Role	IsDenied
NULL	1	Administrator	false
NULL	2	Moderator	false
2	3	News editor	false
3	4	2	true

5. d) Using a single field for Deny

It's much easier to combine the first two fields into one, marking the record implicitly. If the DeniedID is NULL, it means we are working with a regular record, otherwise, we are dealing with a denying record. Information about which Role we are denying is stored in the same field, which works both as a boolean, as an integer.

ApplicationID	RoleID (PK)	Role	DeniedRoleID
NULL	1	Administrator	NULL
NULL	2	Moderator	NULL
2	3	News editor	NULL
3	4	NULL	2

The decision

The last architecture (5. d) is the one I think it's best. It's very transparent and flexible, and it has only one major problem I can currently think of; if only a few (of many) applications would require to share a Role, this wouldn't be possible to solve with this model, but I can live with that.

ApplicationID	RoleID (PK)	Role	DeniedRoleID
NULL	1	Administrator	NULL
NULL	2	Moderator	NULL
2	3	News editor	NULL
3	4	NULL	2

The SQL statement

I have played around a bit with the 5. d) and it works very well. Here's the SELECT statement that retrieves the records for each specific application, supporting the mentioned scenarios.

SELECT Roles.*
FROM Roles
WHERE
(Roles.ApplicationID = 3 OR Roles.ApplicationID IS NULL) --retrieve local and global roles
AND
Roles.DeniedRoleID IS NULL --use only regular records
AND
Roles.RoleID NOT IN --remove denied roles
(
SELECT DeniedRoles.DeniedRoleID FROM Roles AS DeniedRoles
WHERE DeniedRoles.ApplicationID = 3
AND DeniedRoles.DeniedRoleID IS NOT NULL
)

The same query, solved with a JOIN instead of WHERE IN, which is faster, but probably less understandable.

SELECT RealRoles.*
FROM Roles AS RealRoles --regular records
LEFT OUTER JOIN
Roles AS DeniedRoles --denying records
ON RealRoles.RoleID = DeniedRoles.DeniedRoleID --join by the ID of the role
AND RealRoles.DeniedRoleID IS NULL --but only those records that are real
AND DeniedRoles.DeniedRoleID IS NOT NULL --vs those that are denying
AND DeniedRoles.ApplicationID = 3
WHERE
(RealRoles.ApplicationID = 3 OR RealRoles.ApplicationID IS NULL) --retrieve my and global roles
AND
RealRoles.DeniedRoleID IS NULL -- take all regular roles
AND
DeniedRoles.RoleID IS NULL --that don't have a deny

Conclusion

There are many ways to solve a database model in a SaaS architecture, but because of many reasons, we've decided to use a single database with an additional foreign key that defines the client in all the required tables. One of the biggest concerns in this architecture is solving global and local records, but the model presented here takes care of most requirements a typical Saas system might stumble upon, by using a NULL-able ApplicationID, with another field to take care of exceptions that require denying.

This architecture solves most of the problems, and if you're leaning towards a single database architecture, it's probably the best way to go.

The final destination, part 1: technologies and concepts enterprise IT will have to adopt

Mon, 02 Jul 2012 18:38:09 GMT

In the past years, we've witnessed a very important transformation: the consumerization of information technologies. Billions of connected users living their life online, overwhelmed by millions of information systems that have been tailored to suit their every need and desire. Google, Facebook, Apple and Amazon came a long way with their products and infrastructure, but the enterprise isn't losing any time. Learning from the new paradigms and adopting new funky technologies, that have traditionally been developed in corporate laboratories. Can the Fab 4 also predict where enterprise IT is headed? And what will it become?

A few weeks ago I participated in a panel for PI-PL on Ljubljana's Faculty of Economics, where I was asked this exact question: where do I see corporate IT in 10 years. A very hard question indeed, but the more I thought about the it, the clearer it became. Enterprise data, software and technology will sooner or later integrate everything. Simple as that. But to fully understand how this will happen, we must first try to identify the most important trends that have shaped information technologies as we know them today. Yes, most of them don't have that much to do with the enterprise. But things are changing.

Cloud technologies

While cloud computing may have been present in the enterprise for quite some time, it's still pretty much dominated by web players like Google (mail, docs, etc.) and Amazon (hardware), who are also flirting with the enterprise. Who wouldn't? There are massive benefits for businesses to move their stuff to the cloud, from scalable physical Infrastructure to higher level Platform or Software as a service information systems.

Perhaps the most important thing the Cloud achieved was to render technology infrastructure irrelevant. It doesn't matter any more, what kind of environment you use. What type of security, infrastructure, servers and network you have installed. You can outsource these things to others, and it will be much easier and cheaper, while all your migrating-to-a-bigger-thing problems will be solved with a swipe of a credit card. I was fascinated that Microsoft now offers Linux based servers on their Azure cloud services, which can be changed to Windows with a click of a button. Architecture doesn't matter anymore, and this fact helps IT departments to focus on more important things than system administration.

APIs, mashups, platforms and ecosystems

Heavily connected with the whole Cloud concept, data and information never had it easier to travel from one place to another. System migrations (moving data from one information system to another) and system integrations (connecting multiple information systems into one) have always been one of the biggest challenges of IT. But the web didn't have as much resources as the enterprise, so it had to simplify things. By offering APIs (Application Programming Interface), web applications allowed others applications to work with their data in an easy way. Mashups, hybrid information systems built on top of others, were born.

Pretty much every noteworthy web service has its own API. This helped a lot of them to become a platform. You know, like Facebook and Twitter, who have created an ecosystem, where thousands of other applications live around them? Soon, similar concepts will dominate the enterprise too. There are already players like Salesforce, who not only offer business-oriented Software as a service solutions, but the also a Platform for other developers to build services on top of their services. And since everything is so open, all this data can easily be integrated with other information systems or transferred to a different environment. Modern information systems don't have problems with understanding each other, but IT departments have problems with understanding information systems, since different, more business oriented skills are needed to support these integrations.

Mobile devices and new distribution channels

More than ten years ago, when I was an Information Sciences student, there was still a debate going on about the benefits of web based enterprise information systems over traditional Client - Server architecture. In the end, the Web won, mostly because distribution was so easy, you make the update on the server, and every user gets it instantly. Employees need nothing but a browser. They are acquainted with the environment ever since they started using Hotmail, and took it for their own ever since they started using Facebook.

But today, it seems the Web is losing its ground as the leading infrastructure, since a new technology came into town. Capable mobile devices, like smartphones and tablets, now enable access to information systems from anywhere, anytime in real-time. Besides, they arrived with their own app markets, which enabled a whole potential for software distribution, and perhaps more importantly, for software billing. You give a fair share to the store owner, who also promotes your solution, and you can freely focus on developing and marketing the product. It's true that mobile apps may not be as flexible as web applications, since the users need to install the updates (even though this can also be achieved by combining native mobile and hosted HTML 5), but the trend is clear. Apple already has its Mac store, Microsoft Windows will follow soon. Distribution of mobile and Software as a service information systems is becoming trivial.

Big data and The internet of things

Traditionally, banks, retailers and financial institutions have been the organizations that operated with the most data in the world. Well, things are changing, and we can only wonder who owns the most bytes today: is it Google, Facebook or someone else? Since there are less transactions than there are interactions, we can estimate consumer oriented information systems with billions of users are the biggest in existence. While this data is accessible to the enterprise to some extent, there are also hundreds of other systems the enterprise or its employees use, and they all create massive amounts of data and information, which needs to be integrated into a wider picture.

Not only that. Today, there are already are more connected devices that we have initially anticipated. These devices (cameras, sensors, tools, etc.) create even more data, which the enterprise needs to process. This trend of wired gadgets is called The internet of things, and together with the large amount of interconnectable information systems businesses use, points to one important trend: the typical enterprise was never faced with so much data and information, which somehow needs to be integrated and understood in an interdisciplinary way.

New ways of doing things, on a higher level

In the mean time, these consumer oriented (B2C) web corporation not only became a few of the biggest technology companies in existence, they've also invented new ways of how to get things done. From Google's 20%, to flat organizations without management, more and more companies (not only startups) set out to revolutionize how business is done. In the service oriented society, creativity is important, but so is productivity and the ability to ship fast. Done is better than perfect.

With new types of management concepts, such as lean and agile, modern organizations are becoming more and more flexible. Not only in doing things, but also in switching from one technology to another. These companies have developed their own way of thinking about which software to use. And it probably has a lot to do its price, how fast can you start using it, how scalable and connectable it is, and how fast can you dump if for another. IT requirements are moving to a higher level, and information systems have become just pieces of a puzzle that needs to be completed.

Design and user experience

Design and user experience probably still don't have that much to do with enterprise IT, but they are very much worth mentioning nevertheless. Face it, users are becoming more and more demanding, and software developers need to make better and better software. Even though the above mentioned facts are probably the dominating factor for the choice of which information systems the enterprise will use, design and user experience matter more and more.

Businesses have always had problems with implementing new software, educating the users, going through the whole status quo change. But beautiful and useful software penetrates faster. People perceive beautiful things to be more useful, and it's the whole intuitiveness and usability of software that helps them adopt something without too much resistance and problems. Some software vendors already found out user experience is the new competitive advantage, and in the end it may be the thing that tips the scale. But the whole point behind it is that I can see better, more clever and detailed, information systems force out older ones on an even faster pace. The whole world of information systems need reinvention, and user experience design will be the science behind these upgrades. Benefits are becoming more important than features.

Social, crowdsourcing and gamification

More than 3 years ago, I was very excited to present a concept we have been developing in Neolab on Days of Slovenian IT. I called it IT 2.0, since it meant integrating social services into enterprise software (at that time, Enterprise 2.0 was more widely used for stand-alone social software such as wikis or corporate blogs). The truth is, I didn't get the chance to sell it well, and in the mean time, disruptive social services managed to fully find their way into the enterprise. But business won't stop here; there are many other fascinating things the internet has invented that can fully be applied to corporate environments.

Gamification, the art of using gaming mechanics in non gaming environments, is getting more and more claim beyond the web. Both for motivating employees, as for motivating clients. We all like to play, so why should work be any different? And we all like to participate in something bigger, that is why crowdsourcing, where people coproduce something, can bring such exciting results. Can you see where I'm headed?

We are all human, and in the end, behaving on a very basic level. Sometimes we perform better, sometimes worse, and we all hold hidden potential even ourselves aren't aware of. That is what software in the workplace can sometimes help us discover, and it's something more and more businesses are aware of. Social, gamification and crowdsourcing are only a few approaches that can make us feel better and more motivated, and they are all concepts that enterprise IT will adopt sooner or later. Behind every company, there are only people.

The final destination of enterprise IT

These are the concepts and technologies that will shape enterprise IT of tomorrow. And with it, new challenges will emerge, together with new profiles of people, who will understand and use all of the above. These profiles, such as data scientists and business analysts, will help enterprise IT do what it was destined to do: Integrate life, the universe and everything.

But that's another story. Coming up soon.

Check out the complete The final destination series.