0

I have 10 tables without any type of foreign key relations between them. All the tables have the same primary key. In this case, "stock_id" is the primary key. All table columns describe a metric of the stock_id. For eg: product_fees is a column to describe the total fees of stocks. Like this, we have various metrics in all the tables. I use all 10 tables using Django API for the frontend website to show all visualizations (20+ charts). I don't think this design is a good one.

My solution: As all attributes have stock_id I'm thinking of combining the tables into one table which will contain about 110 columns. There is one extra table that will have the prediction data with dates (1000+ rows for each stock) for all stocks. This prediction data will have a foreign key (1:M) to the main table with 110 columns. To improve readability, I am thinking of using 20 views to debug the data in the future instead of scanning through all the column names.

The most important thing is all the values of stocks in the 20 tables change every day and I usually do delete and insert them in each table instead of using updates especially for the stock prediction table with dates.

Is it a good idea to have more than 100 columns and use views for aggregating smaller sections of the table for debugging purposes?

New contributor
Kaushik Ganesan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • 2
    Is there any particular problem, apart from aesthetics, that you're trying to solve by this? If it ain't broke, don't fix it.
    – mustaccio
    2 days ago
0

A single "fat" table will be more efficient than joining 10 separate tables. While the disk IO will be the same in principle, a view will need to perform 9 joins - essentially 9 large sort/merge operations.

Materialized views (https://www.postgresql.org/docs/14/sql-creatematerializedview.html) makes it easy to separate the physical layout of the 'insert/update' and 'select' parts of the application.

create materialized view consolidated_stock_view as
select a.pk, a.measure_1, b.measure_2, c.measure_3 -- etc
  from a_table a
  join b_table b on a.pk = b.pk
  join c_table c on a.pk = c.pk 
  -- etc

When you are finished loading data into the 10 'fact' tables, refresh the materialized view

refresh materialized view consolidated_stock_view;
3
  • 2
    Number of joins is not a direct indicator of how performant a query would be so I'd be careful concluding that "A single "fat" table will be more efficient than joining 10 separate tables". E.g. depending on table structure and index definitions, query depending, a single table may induce a full scan loading the entirety of the data off disk as opposed to getting index seek operations for each table being joined, resulting in more I/O necessary and worse performance with a single table. Good suggestion about materialized views though.
    – J.D.
    2 days ago
  • I agree in principle, but given his stated usecase this is a comparision between select * from view_with_10_joins; and select * from materialized_view and in that case the materialized view will be more performant.
    – matiasf
    2 days ago
  • 1
    Materialized views are definitely a different story for sure. I'd just recommend clarifying your statement around a single table though. Especially for any other readers who don't have the luxury or capabilities to implement a solution with a materialized views. They may false assume to always design wide and denormalized tables.
    – J.D.
    2 days ago

Your Answer

Kaushik Ganesan is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.