Pieter van den Hombergh v1.0 2017-03-25

1. Code style and naming

One of the most important activities in your design and implementation is the choosing of names.

  • In Java that would be for types (classes, interfaces), methods and fields.

  • In the database the names of tables and columns.

Wrong naming is the biggest cause of confusion. Choosing proper names will make you design easily understood.

With each language or paradigm comes a "culture", which defines what is common and expected. The culture between Java on the one hand and databases on the other hand can and do differ.

1.1. Databases have their own culture.

  • Most databases are case insensitive by default. The RDBMS variety in particular.

    • e.g. MySQL uses UPPER case to store table names etc, but will recognize Account or aCcount as the "ACCOUNT" data table.

    • PostgreSQL uses lower case to store table names etc, but will recognize Student or STUDENT as the "student" data table.

    • Because of this uni-case operation, names that are created from multiple words or use prefixes use underscore ('_') as word separator as in student_grades.exam_date for the exam date column in the student grades table.

If you use mixed case, then the database has little trouble understanding what you mean, and you can use upper-lower case to e.g. distinguish between keyword (e.g. SELECT, FROM) and table and column names such as student and grade.

In databases it is considered bad style to used mixed cases for identifiers such as table and column names, because (such as camelCase or Pascal case ):

  • It makes referring to the common objects on the database more work.

    • Using mixed case in the name will need special formatting, such as quoting, and the table is no longer found by the default name style in the database.

    • Accessing the data element with mixed case from e.g. java complicates the matter further, because you need the quoting inside a string, which means that you invariably get to some kind of escaping the quotes used for the names.

mixed case names in java query string
String query = "select \"examDate\" from \"studentGrade\" where ....";
uni case names in Java query string
String query = "SELECT exam_date FROM student_grade WHERE ....";

1.2. and Java (C#) have their own culture too

Java also has its code conventions. Java was invented in a Unix culture at SUN Microsystems, so was case sensitive from the beginning. The naming convention is to use Pascal case for Type names and camelCase for the names of members, with a few exceptions:

  • Identifiers (names of primitive values or instances of classes) are written as fileWriter for an instance of a FileWriter.

  • constants (static final fields) are written in ALL_CAPS with the underscore as partseparator.

1.3. Bridging the naming gap between DB and implementation language.

All the things above are just conventions, not real hard rules. It is as if you would go to London and speak with a Texan accent. They would be understanding you, but also know where you come from…​

There are multiple approaches to bridge the gap.

  • Avoid the gap by choosing names that do not need mixed cases. Then field and column names can be chosen naturally at both ends. (This works even for database dialects that default to UPPER for columns and Java that defaults to lower for fields ). This is the least work, as in none. It only makes the choice of names a bit harder, if you want to stick to the English style of naming things.

  • Drop the convention on one end. As in teach the Texan to speak Cockney.

    • E.g. use uni case and underscores for fields that originate from the database.

    • On the database side, use mixed case with quotes and use the java naming conventions in the database for all tables and columns.

  • Translate as you go.

    • In the marshaling and unmarshaling in the data access layer.

      • At the database side. Add quoting in the query for column names, such that the columns of a query come out as the proper field names on the Java side.

      • In the unmarshaling step, do a translation or mapping from the database name, using some kind of lookup, such as a dictionary (map) with mappings like 'student_grade' ⇒ 'studentGrade'.

It is obvious what the best choice is: Avoid the problem as much as possible. Use short (preferably one noun) names for all the fields. Then the Dutch approach, that is simply concatenate words as in fietspomp (EN: bicycle pump, DE:Fahrradpumpe,Luftpumpe). Dutch style is very much like German in word concatenation, (which they write as 'Wortverkettung') but save on the capitals, hence uni case will do.

If you need name mapping, then my current view is that doing the name mapping is best done as translation on the database side, preferably by using (updatable) views or stored procedures .

  • The database typically has little problems using a mix of quoting characters to define the queries. E.g. PostgreSQL uses single quotes to designate string values, and uses double quotes to 'quote' names, such that they become case sensitive.

example query
select student_grade as "studentGrade" from student;
example stored procedure that does some name mapping.
begin work;
drop type if exists nametupple cascade;
create type nametupple as ("lastName" text, "firstName" text);
comment on type nametupple is 'return type for studentNameQuery';
create or replace function studentNameQuery(in studentId integer) returns setof nametupple
LANGUAGE plpgsql
SECURITY DEFINER
as $studentnamequery$
declare
   r nametupple%rowtype;
begin
    for r in execute 'select achternaam as "lastName", roepnaam as "firstName" from student where snummer ='||studentId loop
        return next r;
    end loop;
return;
end
$studentnamequery$;

commit;

1.4. Other naming conventions.

In a typical RDBMS with proper normalisation tables tend to have a simple numerical primary key, like an int or a long. Often this column is simply called id. This is short and clear, but make writing joins a bit harder. Therefor my recommendation is to use different primary key names, named after the table. Writing a join is a lot simpler and easier to read. You can also use a short join style much more easily. Although this may seem as redundant for the primary key of the table, it eases use of said key in a foreign key expression. Sometime you can even forgo the use of the column name. It gets especially hairy when you have to use quotes in the name aliases using camelCase, to meet the conventions above.

simple versus complex join
select * from student
   join using(class_id)
   where achternaam='Janssen'; (1)
-- versus
select * from student s
  join student_class sc on (s.class_id=sc.id)  (2)
  where achternaam='Janssen';
-- or equivalent using where clauses
select * from student s,
  student_class  sc
  where  s.class_id=sc.id  (3)
  and achternaam='Janssen';

In both examples I assume that the column student.class_id is the foreign key referencing student_class.

1 Uses a common prefix in both tables (primary key in one, foreign key in the other) and one can use the simple join using construction without having to use the fully qualified name of the column (tablename.columnname).
2 Names the id column id in all tables and therefor MUST use a fully qualified name or a alias.columnname syntax. More to write and to err. Best avoid this by table design.
3 Uses the traditional where clause to join the two tables.