Comment by brianpgordon
7 years ago
I have no background in databases so this may be naive or wrong, but the single biggest pain point in SQL that comes to my mind is that it can be difficult to tell what a query is doing without also knowing the constraints on the tables involved. Here's a real-life (ish) example from work:
We have some_table which we want to join to other_table, but we need to map an identifier through mapping_table in order to do it. So we end up with a query like:
SELECT (...) FROM some_table INNER JOIN mapping_table ON (...) INNER JOIN other_table ON (...) ...;
I know for sure when writing this query that the middle join to mapping_table will map every some_table row to exactly one row (no more, no fewer) in mapping_table. The problem is that the query doesn't capture this. The mapping table isn't really named something as obvious as "mapping_table" so someone reading the query has a hard time inferring what the intent was. It totally changes how you mentally parse and think about the query if the result set can be accumulating multiple matching rows from the join, or maybe even losing rows if there are no matches. You have to go bring up your database schema to figure this out.
And, as a fan of static typing, I can't help but cringe at the possibility of someone changing the constraints on the table without realizing that there are queries which implicitly depend on the old ones. SQL offers no resilience to this and will happily change the meaning of your query without a peep of complaint if you drop that constraint from mapping_table.
If there's a fancy way to capture this "mapping" relationship in standard SQL that doesn't just use a dumb inner join, I'd love to know about it. If not, I'd love a query language that supports some annotations that help reading and are either stripped out before sending to the database engine, or are actually checked at runtime.
If I understand correctly you are describing a one to one or one to many relationship. The canonical way to express that is 'don't use a mapping table'. Mapping tables are for many to many relationships. So why is there a mapping table in you example, and could you just get rid of it altogether?
In this case it's tying together data from different, independent systems. So the schema may not be textbook ideal but I don't think there's any way around it.
If you really must use a mapping table, I think you could put a unique constraint on both of the foreign key columns too the mapped tables. That would enforce that every row in one table maps to at most one row in the other
I don’t think that fundamentally changes the nature of a one to many relationship though. Unless your mapping table has a variety of different columns to join on.
Can you use views or other feature to tie together a table and its mapping table into a single view?
> I know for sure when writing this query that the middle join to mapping_table will map every some_table row to exactly one row (no more, no fewer) in mapping_table.
Your example query joins one some_table row with 1-n rows on mapping table, and another 1-n rows from whatever else is in there to that. If you're expecting a single row in the resulting set per some_table row, it means that you're filtering very hard (which is fine) or that you've a schema problem (which is the actual problem).
In CQL (http://categoricaldata.net), which generalizes relational theory with category theory, you can annotate schemas with equations and have them checked at runtime, or at compile time with an automated theorem prover (e.g., to establish that a query into a schema with a constraint will always materialize an instance that satisfies that constraint). One example is de-normalization: https://www.categoricaldata.net/denorm.php
I get that with some queries, you're not sure how the data is going to be retrieved, and letting the database figure all of it out for you is a good strategy. But with a lot of queries, particularly the ones that I'm doing in the milliseconds of a pageload, I want to be very sure that all my joins are hitting efficient indexes. I hate that someone can change my schema, and that can turn my efficient index lookup into a horrible scan, without breaking tests.
Folks who know more SQL than me: Is there a good way to say "I would rather this query fail than try to scan a table?"
> I hate that someone can change my schema, and that can turn my efficient index lookup into a horrible scan, without breaking tests.
They can't, if you do perf testing as part of your pre-release testing, which you should do.
If you aren't doing perf testing, you are saying perf isn't an acceptance criteria, so why are you upset that breaking perf doesn't break tests?
Where you start to go wrong is where your talk starts to be of "YOUR" schema. The schema isn't yours, it's the company's. And guarding it is the DBA's job.
(I understand full well that that is a problem if the company has kicked out the DBA role and handed it over to the individual programmers, but perhaps that is precisely the problem.)
Fair, though maybe I could describe the same problem from the other end. If I'm a DBA and I want to change the way indexing is done, I might need to audit every query in my company to figure out which ones depend on the old index. One way or another, it seems like knowledge about a query's intent to use an index could've been captured explicitly, in a way that's automatically enforced in the future against any number of accidental breaks. But instead all that knowledge is implicit.
1 reply →
I am quite sure other implementations of SQL have similar tools but from my support of an iSeries.
The SQL scripting function is a tool run from a desktop, all emulation and such is JAVA based, with the feature to ask the system what the query is doing. The feature called Visual Explain will explode the query into a graphic representation of how the system optimized it to run. It will recommend indexes as needed. This is very good for understanding when table scans are forced, how files actually joined up, and more.
> And, as a fan of static typing, I can't help but cringe at the possibility of someone changing the constraints on the table without realizing that there are queries which implicitly depend on the old ones
When creating views and sprocs, SQL Server lets you mark them as 'schema bound', creating dependencies on the schema objects it uses.
Not sure if something like this exists in Postgres?
All views in PostgreSQL are schema bound, which is usually good but can be annoying at times.
Ah, I didn't know that!