SQL. Create one big table or different tables for earch client "client stock portfolio"? I-CodePudding

What is better for multiply clients? I create training project and can't understand what's better. Create one big stock portfolio table for all broker's clients or create individual table for each client? Individual table will require add brokerage agreement id for each table's name for it indentification.

DROP TABLE IF EXISTS portfolio;
CREATE TABLE common_portfolio (
   common_portfolio_id serial,
   brokerage_agreement_id BIGINT UNSIGNED NOT NULL,
   type_assets_id BIGINT UNSIGNED NOT NULL,
   stock_id BIGINT UNSIGNED NOT NULL,
   stock_num BIGINT UNSIGNED NOT NULL,
   
   FOREIGN KEY (brokerage_agreement_id) REFERENCES brokerage_agreement (brokerage_agreement_id),
   FOREIGN KEY (type_assets_id) REFERENCES type_assets (type_assets_id),
   FOREIGN KEY (stock_id) REFERENCES stock (stock_id)
);

DROP TABLE IF EXISTS portfolio_12345612348; -- number generate from brokerage_agreement_id
CREATE TABLE portfolio_12345612348 (
   position_id serial,
   type_assets_id BIGINT UNSIGNED NOT NULL,
   stock_id BIGINT UNSIGNED NOT NULL,
   stock_num BIGINT UNSIGNED NOT NULL,

   FOREIGN KEY (type_assets_id) REFERENCES type_assets (type_assets_id),
   FOREIGN KEY (stock_id) REFERENCES stock (stock_id)
);

CodePudding user response：

It is always better to keep all them in same table.

Keeping each client's data in a separate table will provide you with best performance only in case when you're looking for this particular customer.

But in all other cases it will be hell: creating/deleting a client will require you to build a dynamical create/drop table statement.

When sometime later you decided to add a column, you'll need to find ALL of those tables somehow and add new column to each one of them.

Even counting number of clients will cause you to write way more code rather than just "select count" statement.

And many more cases

So, use only one table