3 ms·

> "select f.bar_id->some_bar_table_column" Something like this was actually my first iteration of this idea, to join based on the column name(s). But then some

> "select f.bar_id->some_bar_table_column" Something like this was actually my first iteration of this idea, to join based on the column name(s). But then someone helped me think about what if the column is involved in two different foreign keys (to two different tables)? Not common, but absolutely possible. That's why just naming the column(s) isn't precise enough, except in most cases. Even if no such ambiguity was allowed, i.e. if all unique sets of column name(s) referenced exactly one table, you would still need to think about how the syntax would look like for foreign keys on multiple columns.

zmmmmm 5y ago
I guess this comes back to the whole concept of something being ergonomic - how well is it designed to comfortably enable the common use case vs. forcing users to pay a tax and do something annoying on the 95% case to allow for the 5% case to work. The scenario you mention is rare enough in my experience compared to a simple DAG style hierarchical table structure that we should totally optimise the query language for the former and then just add in escape hatches for the less common scenarios. I'm sort of surprised that there is nothing similar to the CSS SASS/SCSS type idea that has ever taken off in SQL land ...
- JoelJacobson 5y ago
  I don't think something that works in 95% of the cases would be accepted by the SQL committee. I think at least you would need to find a way to follow foreign keys on multiple columns. I can see why working with column names would be nice, since we then would not have to deal with foreign key names at all, which is another thing to keep track of mentally. But the only way that would work would be if we would forbid creating multiple foreign keys on the same set of columns, only then would the set of columns uniquely identify what foreign key to follow. This is much more invasive though, as it could break existing data models.

zmmmmm 5y ago

I guess this comes back to the whole concept of something being ergonomic - how well is it designed to comfortably enable the common use case vs. forcing users to pay a tax and do something annoying on the 95% case to allow for the 5% case to work. The scenario you mention is rare enough in my experience compared to a simple DAG style hierarchical table structure that we should totally optimise the query language for the former and then just add in escape hatches for the less common scenarios. I'm sort of surprised that there is nothing similar to the CSS SASS/SCSS type idea that has ever taken off in SQL land ...

JoelJacobson 5y ago
I don't think something that works in 95% of the cases would be accepted by the SQL committee. I think at least you would need to find a way to follow foreign keys on multiple columns. I can see why working with column names would be nice, since we then would not have to deal with foreign key names at all, which is another thing to keep track of mentally. But the only way that would work would be if we would forbid creating multiple foreign keys on the same set of columns, only then would the set of columns uniquely identify what foreign key to follow. This is much more invasive though, as it could break existing data models.