How to design a database with dynamic fields?

Question

PostgreSQL

I want to implement storage of the user's investment portfolio (A user's portfolio can store a variable number of shares). What would be the right decision? Do I need to use Json files to store this portfolio?

Answer 1

Simplified version:

CREATE TABLE users (
  id serial primary key,
  	name varchar(255)
);

INSERT INTO users VALUES (1, 'John'), (2, 'Sarah');

CREATE TABLE shares (
  id varchar(12) primary key,
  	price numeric
);

INSERT INTO shares VALUES ('TSLA', 781.30), ('AAPL', 105.00);

CREATE TABLE user_shares (
  user_id int,
  	share varchar(12),
  	count int
);

INSERT INTO user_shares VALUES (1, 'TSLA', 5), (2, 'AAPL', 1);

SELECT 
  users.*,
  shares.*,
  user_shares.count,
  user_shares.count * shares.price total
FROM user_shares
JOIN users ON user_shares.user_id = users.id
JOIN shares ON user_shares.share = shares.id
;

PostgeSQL fiddle

Answer 2

You need to learn the basics of relational databases, because here you need not "dynamic fields", but a relationship, this is basic knowledge.