Naming Convention

As we may know, by the naming convention, we can reduce a lot of efforts/headaches in different kinds of configuration/mapping by annotation/xml or even in codes. Naming convention makes our codes consistent, integrated and much cleaner.

Here are the three naming policies mostly used by people to name database tables/columns:

CREATE TABLE account(
    id bigint(20) NOT NULL AUTO_INCREMENT,
    gui varchar(64) NOT NULL,
    first_name varchar(32) NOT NULL,
    last_name varchar(32) NOT NULL,
    status int NOT NULL DEFAULT 0,
    last_update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    create_time timestamp NOT NULL,
    UNIQUE (gui),
    INDEX first_name_ind (first_name),
    INDEX last_name_ind (last_name),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8;

• Upper case with underscore:

CREATE TABLE ACCOUNT(
    ID bigint(20) NOT NULL AUTO_INCREMENT,
    GUI varchar(64) NOT NULL,
    FIRST_NAME varchar(32) NOT NULL,
    LAST_NAME varchar(32) NOT NULL,
    STATUS int NOT NULL DEFAULT 0,
    LAST_UPDATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CREATE_TIME timestamp NOT NULL,
    UNIQUE (GUI),
    INDEX LAST_NAME_IND (FIRST_NAME),
    INDEX LAST_NAME_IND (LAST_NAME),
    PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8;

• Capitalize from the second word, just like naming convention in Java:

CREATE TABLE Account(
    id bigint(20) NOT NULL AUTO_INCREMENT,
    gui varchar(64) NOT NULL,
    firstName varchar(32) NOT NULL,
    lastName varchar(32) NOT NULL,
    status int NOT NULL DEFAULT 0,
    lastUpdateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    createTime timestamp NOT NULL,
    UNIQUE (gui),
    INDEX firstName_ind (firstName),
    INDEX lastName_ind (lastName),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8;

All of them are good naming policies and supported in abacus-util. However, we recommend "lower case with underscore" more than other two policies(Maybe, just because of personal experiences).

Here is also a bad example:

CREATE TABLE account(
    id bigint(20) NOT NULL AUTO_INCREMENT,
    gui varchar(64) NOT NULL,
    first_name varchar(32) NOT NULL,
    status int NOT NULL DEFAULT 0,
    last_name varchar(32) NOT NULL,
    lastupdatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CREATE_TIME timestamp NOT NULL,
    UNIQUE (gui),
    INDEX first_name_ind (first_name),
    INDEX last_name_ind (last_name),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8;

1, Different naming policies for "first_name", "lastupdatetime", "CREATE_TIME". 2, it's hard for program to judge "lastupdatetime" is one word or a composite of several words. 3, columns are not ordered properly: "first_name", "status", "last_name"

Although it's not mandatory, we strongly recommend that all the tables/columns should be named with a consistent naming policy and organized with proper order. Any thing messed up here, it will also mess up the auto-generated classes, and you codes, and json/xml you may need, and... your heads and other people's... We think it's important and worth to spend couple of hours or even days on defining a good table or domain model. Every minute spend on it will be ten times rewarded later.