Save different account types in a database

Question

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?

Answer 1

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.

Answer 2

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

Until it becomes a nuisance you can extend the same table. But, this is totally sound and permissible design. Below is valid PostgreSQL which provides an indexable JSON type with custom operators, jsonb.

CREATE TABLE accounts (
  accountid       serial,
  name            text    PRIMARY KEY,
  account_type    text,
  login           text    UNIQUE CHECK (passwd IS NOT NULL),
  passwd          text    CHECK (login IS NOT NULL),
  profileurl      text,
  serverspecific  jsonb
);

I think planning too far when you're too new, or your app is too new is a detriment. The more complex your schema, the harder it'll be to make use of it early on.