wood burning stoves 2.0*
The moose likes JDBC and the fly likes key-columns naming conventions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "key-columns naming conventions" Watch "key-columns naming conventions" New topic
Author

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).


http://home.arcor.de/hirnstrom/bewerbung
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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

all:
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?


SCJP, SCWCD
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 ]
 
Consider Paul's rocket mass heater.
 
subject: key-columns naming conventions
 
Similar Threads
Insert row in a table which having No primary Key
Problems using GenerationType.IDENTITY for in-memory databases
many:many relationship
I need a bit of explanation about bidirectional relation between two entity beans.
Binding Database Tables in Desktop App