CODEWITHSUNDEEP
sql
Philip
Philip

Reputation: 41

Duplicating similar queries

In my project I have very similar queries and the only difference is one table in each query, like this example

--queries
select t1.mycolumn
from tb_america t1 --only difference
inner join table2 t2 on t1.field = t2.field
inner join table3 t3 on t3.field = t2.field

select t1.mycolumn
from tb_asia t1 --only difference
inner join table2 t2 on t1.field = t2.field
inner join table3 t3 on t3.field = t2.field

--or

@varcountry = param ? 'tb_asia' : 'tb_america';

select t1.mycolumn
from @varcountry t1
inner join table2 t2 on t1.field = t2.field
inner join table3 t3 on t3.field = t2.field

I created as two separate queries since in my understanding they are not the same query, my question is, does this break DRY principle? I could have just replaced the table name, but I am assuming the risk if only one query changes I'll have to separate them again or add conditions for each client leading to messy code and maybe even breaking KISS principle.

What should be the correct approach to this situation?

PS: This is client database so the tables cannot be changed

Upvotes: 0

Views: 90

Answers (1)

Jochen Reinhardt
Jochen Reinhardt

Reputation: 843

I don't think that there is a correct approach for this case.

The current situation seems to be not that common. You have two tables that have the same structure so your queries look the same for your tables. But this structure is not enforced in any way, so theoretically, one of your tables could change and break your query. So the status quo, that cannot be changed, is the actual issue. There can be no correct solution with this kind of flaw in the setup.

Do you break the DRY (don't repeat yourself) principle when you have tow separate queries for two different tables? Arguably, no, IMHO.

When you have a parameterized query, where you have to provide input to either query the one or the other table - you used a boolean expression, this does not look that simple any more. This is not KISS anymore.

And what would happen if in the next year, they'd introduce tb_europe? Or if there were additional relations for america that would not be available in asia? In terms of maintainability and extendability, separate queries would be preferable.

If, however, all of your tables change in the same way and nothing unexpected happens, you can save a couple of key-strokes if you only have to change a single parameterized query.

Personally, I would prefer multiple simple queries over a single parameterized query that is built on assumptions of tables having similar structure.

The flaw in your database schema is two tables that have the same structure (organizationally managed) and I guess, store the same data for different regions. A better approach here would be to use a single table for all the regions, add a region column to the table and define partitions accordingly. See https://dev.mysql.com/doc/refman/8.0/en/partitioning.html That would allow you to use a single SQL for all your queries / regions.

Upvotes: 1

Related Questions