More tables or More records performance wise

Posted on

Question :

i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below:

  id
  url
  region_1
  region_2
  region_3
  region_4
  date_created

My colleague creates a table in each category a url appears so the database looks like this

angular
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 react
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 asp

Same schema and it goes on and on…..

my proposed schema will look like this

sites
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created
   tech_category

This would satisfy the ORMs (Dapper/EF) on the application level to be more maintainable and will just use a single table. What is the right approach? Is it more favorable to create a single table to hold the data or is multiple tables with smaller sets of data?

Answer :

Both of these schema anti-patterns have been repeatedly refuted:

  • Do not have multiple ‘identical’ tables.

  • Do not spread an array across columns.

So have one table with lots of rows:

    sites
       id
       url
       region
       value
       date_created
       tech_category

Use suitable composite indexes for efficiency. (We need to see your SELECTs to advise further.)

Use “pivoting” to make the 4 region columns. (Or >4 when you increase the number of regions.)

As for “performance”… If you don’t have a billion rows, performance is probably not a problem. When the table gets that big, we can discuss “summary tables”.

Leave a Reply

Your email address will not be published. Required fields are marked *