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