Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

key-columns naming conventions

 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After rereading the documentation I found

which seems pretty helpful.

Thanks all. Especially to thunderstorm Jeanne.
[ September 22, 2004: Message edited by: Stefan Wagner ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic