File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes key-columns naming conventions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "key-columns naming conventions" Watch "key-columns naming conventions" New topic

key-columns naming conventions

Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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.

Is it possible with sql?
With jdbc?

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).
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

Many thanks, Jeanne.
I will look today (after getting some sleep now ) for metadata:getPrimaryKey.

I'm still interested in opinions, whether the naming-conventions, mentioned above, are mostly used in professional and large circumstances.
Edwin Keeton
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

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?

Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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.

Between 20% and 50% percent I'm unsure.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

After rereading the documentation I found

which seems pretty helpful.

Thanks all. Especially to thunderstorm Jeanne.
[ September 22, 2004: Message edited by: Stefan Wagner ]
I agree. Here's the link:
subject: key-columns naming conventions
jQuery in Action, 3rd edition