Database Design – Save Different Account Types in a Database

database-design

I have the following issue. In one application (imagine a messaging/e-mail app on your smartphone), the user enters its accounts and gets information from the server displayed. I need to store the accounts and there are different types of accounts. Something like this:

  • All account types: name of the account, account type, …
  • Additional fields for account type A: user, password, URL
  • Additional fields for account type B: user, password, server specific info

I expect to have 1-5 accounts per user, so really small table(s). What I need to consider is that account types may be added/removed in the future, so I want to avoid referencing the account types or their specific fields in the main DB handler.

The best idea that I have right now is to store everything in the same table. This will contain the fields that are common to all account types and an additional text field with the remaining information (e.g. in JSON format).

What is the general/best way for solving the issue? Is my approach the correct one?

Best Answer

You can reasonably use both approaches. The majority of contemporary SQL databases (mySQL, PostgreSQL, Oracle, SQL Server) will recognize JSON as a data type, and will let you store it as such (that is: it is not just a string, but a string following a certain number of rules).

Things to consider to make your choice:

  1. How the different parts of the data are accessed: If, whenever you need to check the main table, you also need to access the surrogate one (to get the remaining information, regardless of which one it might be), then it is a good idea to have all the information together. If most of the accesses to the main table don't need that remaining information, having it in several separate tables makes more sense.

  2. Access permissions (privileges): If anyone having access to the main table doesn't need or shouldn't have access to the reamining information, then, it feels more natural (and is probably easier) to have it in two (or more) different tables, and deal with table permissions, instead of column permissions.

  3. Programming language ease of handling the JSON data: You can access JSON data from JavaScript, Python, C#, Java, Ruby, PERL... and most (if not all) programming languages. But is not equally easy with all of them. So, consider if the programming language for your project handles JSON easily, or you prefer to get always atomic data (columns with ints, floats, texts, dates and times, ...) from database rows. If handling JSON is not straightforward, choose surrogate tables.

  4. Modularity: if you plan to work with different modules (or classes, or ...) for different types of accounts, it may make sense that each module gets a user_id, and it takes care to check/update one table extra_user_info_X by itself (where X is the type of account). This approach would better match the modularity of the application design.

If your database allows for inheritance (like PostgreSQL or Oracle), that might also be a pattern/paradigm that fits your needs, and a complement to using a multi-table approach.

Related Question