Databases
StackOverflow2013
Postsdbo
POMulitple Tables vs Tables Indexes in Postgres
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMulitple Tables vs Tables Indexes in Postgres
    primarykey
    data
    text
    <p>I'm a front end developer with little SQL experience. I am considering developing a data query system for an organization I work for.</p> <p>Currently much of the data is in a series of spreadsheets. I have close to 100 worksheets (ie tables) derived from the same template (including a column for country) but with different values based on the worksheets' planning scenario (eg "efficient") and economic sector (eg "agriculture"). Each worksheet has about 8000 rows.</p> <p>Do I create separate database tables for each of these worksheets - even through the tables will have identical <code>CREATE</code> statements? In this case I imagine I would create an index along these lines: </p> <pre><code>CREATE INDEX sector_scenario_lower_country ON sector_scenario(lower(country)); </code></pre> <p>I would have to create this index 100 times (once for each sector_scenario table). When I want to find the data rows I'm looking for, I'd have to use my app to identify the correct table (this really shouldn't be much trouble or take much time) and then make the query:</p> <pre><code>SELECT col4, col5, col6 FROM sector_scenario WHERE lower(country) = "brazil"; </code></pre> <p>Or should I add columns for scenario and sector to the database table and then copy all of the worksheets to that single table?</p> <p>In this case, I'd create the following index just once:</p> <pre><code> CREATE INDEX main_table_idx ON main_table(scenario, sector, lower(country)); </code></pre> <p>I'd then make the following query quite regularly:</p> <pre><code>SELECT col4, col5, col6 FROM main_table WHERE scenario = "efficient" AND sector = "agriculture" AND lower(country) = "brazil"; </code></pre> <p>Obviously the second option will be much less work to set up. However, can I expect comparable performance from it?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USEric H.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThe correct answer depends on what the data represents and how it is to be used. I've heard good things about the book, Database Design for Mere Mortals. It might help you identify some considerations.
      singulars
      1. POMulitple Tables vs Tables Indexes in Postgres
      1. USDan Bracuk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload