Multiple components data storage in one table

Question

I am developing a project that helps administrators store applications infrastructure information in one place. During planning phase I faced with problem how to store & process some components information in the best possible way.

For example, administrators can create an Application and add to it some Services like (Web App, Lambda, Win Service etc). To each Service administrator can assign Components of different types:

1. API Components (fields: URL, Key, version etc)
2. Okta Component (field: Issuer URL, callback, Application ID, Secret Key)
3. Umami (field: Environment, Script, Link etc)
4. ....

Approximate application hierarchy

I thought to create table called Components with columns like Id, ServiceId, Data, Type and store in Data field all component related data in JSON format converted to base64 and serialize/deserialize it each time when needed. The key idea that I am going to extend application later and would like to have possibility easily add new components without core code modifications.

Could you suggest some good approaches to achieve desired application behavior?

Thank in advance.

Answer 1

Your approach with json Data is already very extendable. It is enough to make a polymorphic handlers for json Data. And adding a new component will require only a new handler, without changing the database and the rest of the infrastructure.

However, it is better to store json not in base64 but in specially designed types like postgres jsonb. It's more efficient and easier to manipulate.

There are 3 small problems that can arise with this design.

1. Changing the json structure. For example adding a new field. This can be solved by dynamically changing the deprecated structure during a normal query.
2. Concurrent queries. When 2 queries are trying to change one json at the same time. The problem occurs because the json is handled on the application server. Can be solved by using idempotency key.
3. A query that filters records by fields within the json. Specialized types like postgres jsonb will allow you to do this directly in the database, but it will still be slow. But as far as I understand your tasks, you won't need such queries.