4 ms·

In some sense, what you're asking for is straightforward with the tools we already have. It suffices to set a convention that each table always has a column nam

In some sense, what you're asking for is straightforward with the tools we already have. It suffices to set a convention that each table always has a column named `id` which is the primary key for the table. The harder part is how to enforce that within an organization. It sounds like you'd like technology to enforce it. Existing tools already do the math part: you can set a constraint on a table so that the database maintains the primary key property and throws an error if a transaction would change the table in a way that violates the property. What you're left to do is get everybody on board with the "every table has a primary key column named id" plan. Some syntactic sugar like GROUP INTO might help with that.

dragonwriter 3y ago
Looking at this again, I think the actual complaint isn't so much about base tables (though those were used in the illustration) but intermediate derived relations created in deeply nested queries (or even regular views), where even though their may in effect be primary/unique keys, they aren’t declared and recognizing them depends on tacit knowledge (and because the functional dependencies aren't recognized by the DB engine, they can’t be leveraged in GROUP BY to omit redundant non-key columns so a GROUP BY needs to specify all the non-aggregate columns with the domain understanding being opaque. A primary key convention for base tables doesn’t help with this; I also don't think the propsed GROUP INTO solves it, though it requires it to be solved first to work (i.e., unless you are only using it to GROUP INTO base tables rather than intermediate tables formed by arbitrary joins, it requires first having the engine infer, or provide a way of declaring and having the engine validate, keys for those tables.)
- civilized 3y ago
  Honestly, I'm not sure what all this means. Maybe an example would help? It sounds like there's an interest in the database inferring something subtle and making some kind of automated decisions based on that. Business stakeholders often make this kind of request - "can't an AI just figure all this out?" kind of thing. It often doesn't go anywhere because it's too far removed from the level of detail needed for a machine to automatically solve a problem.

dragonwriter 3y ago

Looking at this again, I think the actual complaint isn't so much about base tables (though those were used in the illustration) but intermediate derived relations created in deeply nested queries (or even regular views), where even though their may in effect be primary/unique keys, they aren’t declared and recognizing them depends on tacit knowledge (and because the functional dependencies aren't recognized by the DB engine, they can’t be leveraged in GROUP BY to omit redundant non-key columns so a GROUP BY needs to specify all the non-aggregate columns with the domain understanding being opaque. A primary key convention for base tables doesn’t help with this; I also don't think the propsed GROUP INTO solves it, though it requires it to be solved first to work (i.e., unless you are only using it to GROUP INTO base tables rather than intermediate tables formed by arbitrary joins, it requires first having the engine infer, or provide a way of declaring and having the engine validate, keys for those tables.)

civilized 3y ago
Honestly, I'm not sure what all this means. Maybe an example would help? It sounds like there's an interest in the database inferring something subtle and making some kind of automated decisions based on that. Business stakeholders often make this kind of request - "can't an AI just figure all this out?" kind of thing. It often doesn't go anywhere because it's too far removed from the level of detail needed for a machine to automatically solve a problem.