I like to know, whether it's possible to get relation-informations from a database, how a table is joined (semantically) with another.
Example (* indicating primary key):
A user (not a dba) uses a program, and needs informations from orders and product_description. He's not a teached in databases, and the program shall find the relation between orders and product_description on it's own.
In this special case, the naming is helpfull - order_id and product_id use the same name in the table, where they are primary keys, as well as where they are foreign keys.
I know how to get all table- and column-names with metadata, and of course I could try to search for the shortest connection via columnnames between two tables.
But this can be a lot of work for a huge database (reminds me of the travelling-salesman-problem). It might lead to wrong results, when columnnames match unintentionally. It might lead to no result, when the creator of the tables didn't use such naming-conventions.
Are such naming conventions very common in the sql-field?
(preferred: a hint for a common solution over a special oracle-solution over something else).
Stefan, Metadata could also tell you whether a field is a primary/foreign key. Only these fields would be eligible for joins. You would only have a problem if you have two sets of keys with the same name. But that would be confusing in general and should be avoided.
So you could do this with a combination of Java and JDBC/SQL. However, there may be an easier way. If the schema doesn't change often (as is likely), you could just have a property file with the primary/foreign key lists.
I strongly recommend not relying on naming conventions. Naming conventions are a good idea, even a best practice, but they aren't infallible.
You should also know that you can legally join two tables on any columns, regardless of whether the columns are primary keys, foreign keys, or indexed, as long as the datatype of the joined columns is the same.
Could you use a view of the joined tables rather than trying to join them dynamically?
Well - I write a software, which shall run in professional environments and help relative unprofessional users. So I cannot prepare the database with a selfmade relation-table. (Well, I can do it with userassistance, to make second usage more easy).
But if such naming-conventions are used in 50%-80% of the cases, it would be good enough. If they are only used in about 20%, it's a too weak convention.