How do I map tables with n columns to a database?

Question

We are currently using PostgreSQL, now have to save some tables in a database. The tables are never updated once created, but may be filtered.

The tables are dynamic in nature, as there may be n columns,

so a table would be:

|------|--------|--------|
| NAME | DATA 1 | DATA 2 |
|------|--------|--------|

another table would be:

|------|--------|--------|--------|--------|--------|
| NAME | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 |
|------|--------|--------|--------|--------|--------|

The data is not normalized because it hurts when dealing with n rows as all rows are read all at once.

These are the solutions that I come up with,

1. Save the table as JSON in a JSON Type or HStore pairs.
2. Save the table as CSV data in a Text Field

What are the alternative methods to store the above data? Can NoSQL databases handle this data?

Answer 1

I see nothing in your question that would keep you from using plain tables with the according number of data columns. That's the most efficient form of storage by far. Smallest storage size, fastest queries.

Tables that are "never updated once created, but may be filtered" are hardly "dynamic". Unless you are withholding essential details that's all there is.

And unless there can be more than several 100 columns. See:

• What is the maximum number of columns in a PostgreSQL select query

(But you later commented a maximum of 12, which is no problem at all.)

Answer 2

From what you've described, it sounds like a job for jsonb. Assuming name is unique in a certain table, I can imagine sth like this:

create table test (
  tableId integer,
  name text,
  data jsonb,
  constraint pk primary key(tableId, name)
);
insert into test values (1, 'movie1', '{"rating": 10, "name": "test"}');
insert into test values (1, 'movie2', '{"rating": 9, "name": "test2"}');
insert into test values (2, 'book1', '{"rank": 100, "name": "test", "price": 10}');
insert into test values (2, 'book2', '{"rank": 10, "name": "test", "price": 12}');

Basically the idea is to use tableId to identify each sub-table and store rows of the subtables in this one db table.

This opens some possibilities:

• create a separate table to store metadata about each sub-table. For example, schema of the sub-tables could be stored here for application layer validation.
• partial index on large/hot sub-tables: create index test_1_movie_name on test ((data->>'name')) where tableid = 1

Answer 3

Dynamic column means schema less is the option we should look for . MongoDB is preferred. Are we storing as JSON ? If so Mongo will help manipulatin data / extracting / reporting will make life easier.

If you are not familiar with NOSQL . MSSQL 2016 onwards JSON storage in column is supported as varchar(MAX). SQL Server is providing the functions to deal with JSON data. Even though its a text based index by default for nvarchar . SQL supports computed column based indexing which will help to handle the elements look in JSON. Any number of non clustered index computed column is allowed which will ease the indexing to handle JSON data. SQL 2019 has more support for JSON