Best practice: best database naming convention for JPA?

Question

In Java the naming convention for properties en classes (entities) are done the CamelCase way:

@Entity 
public class UserMessage implements Serializable { 
    @Id 
    private Integer id; 
    private String shortTitle;
    private String longTitle;
    private String htmlMessage; 
} 

But in the SQL world it’s considered a best practice to use upper case with underscores between words (like Java constants). In the SQL world is also considered a best practice to include the table name in the column names, this way foreign keys are in most cases named exactly the same as the id in the original table.

CREATE TABLE USER_MESSAGE (
    USER_MESSAGE_ID  MEDIUMINT(8) NOT NULL,
    USER_MESSAGE_SHORT_TITLE VARCHAR(20),
    USER_MESSAGE_LONG_TITLE VARCHAR(80),
    USER_MESSAGE_HTML_MESSAGE TEXT NOT NULL
); 

Should I follow both standards and use the name attribute on @Table and @Column? Or should I follow the Java conventions and rely on the default JPA mappings.

What is the most common approach and/or the best approach on this conflict of standards?

Answer 1

Follow both. The db convention should be there for DBA sake and manual reports and queries where the mind set is different. Use the name params on annotations to achieve this.

Answer 2

I suppose that this depends on whose conventions you're referring to. I do not put the table name into the column name - what's the point of losing half your namespace just to repeat what you already know? (Some of) the rules I (try to) follow are:

1. Long, meaningful names are better than short names, e.g. TRANSACTION_DATE rather than TRAN_DT. Yes, I'm old enough to have written Fortran when you were limited to 6-character variable names, and I recall Basic variants where you only had A-Z, A0-Z0...A9-Z9 - but I'm also old enough to have learned better. Single-character variable names for indices, etc, are fine - and in fact traditional - but when I find a function with twelve single-letter variable names each used for multiple purposes I...am not amused.

2. Artificial primary keys are named ID_<<"name of table">>.

3. Single-field natural data primary keys are best. Two-field natural primary keys are OK. Three or more fields - create an artificial primary key and make the natural key an alternate unique key.

4. Thou shalt never, ever, ever count on a date, time, or date/time field to be unique. Ever. Don't forget this. I mean it.

5. Obfuscatory coding techniques are equivalent to incompetence.

I'm sure there's more, but it's a start. All IMHO. YMMV.

Share and enjoy.

Answer 3

As far as I'm concerned either are acceptable. But if you decide you don't want the default camel case, you CAN get a different naming strategy without resorting to the tedious and error-prone task of adding the name attribute to every annotation.

Take a look at Hibernate's org.hibernate.cfg.ImprovedNamingStrategy class. It uses underscores instead of camel case. It is simply a matter of setting a property on your Hibernate configuration to use it.

You could also extend the ImprovedNamingStrategy to prepend the table name or do all uppercase if you really want, but that seems unnecessary.

Answer 4

Should I follow both standards and use the name attribute on @Table and @Column? Or should I follow the Java conventions and rely on the default JPA mappings.

If the JPA default conventions don't match the preferred conventions of your company (there is no "one true" standard), override them. This can be done using the @Table and @Column annotations (in the particular case of Hibernate, you could also provide your own implementation of a NamingStrategy).

What is the most common approach and/or the best approach on this conflict of standards?

There is no conflict, there are Java naming conventions, there is one default convention on the JPA side for the mapping of objects to tables (because JPA had to pick one) and there is no "one true" standard on the SQL side. So:

• if your company doesn't have any SQL naming conventions, you could use the JPA conventions
  • if you don't like them, override them
• if your company has conventions in place, follow them and override the JPA defaults