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?
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”.