database, table and column naming conventions

Question

Hello,

Do you know how to use the naming conventions in mysql database? I've downloaded a mysql sample database.

Here it is:

CREATE DATABASE IF NOT EXISTS classicmodels  DEFAULT CHARACTER SET latin1;
USE  classicmodels ;
DROP TABLE IF EXISTS  customers ;
CREATE TABLE  customers  (
   customerNumber  int(11) NOT NULL,
   customerName  varchar(50) NOT NULL,
   contactLastName  varchar(50) NOT NULL,
   contactFirstName  varchar(50) NOT NULL,
   phone  varchar(50) NOT NULL,
   addressLine1  varchar(50) NOT NULL,
   addressLine2  varchar(50) default NULL,
   city  varchar(50) NOT NULL,
   state  varchar(50) default NULL,
   postalCode  varchar(15) default NULL,
   country  varchar(50) NOT NULL,
   salesRepEmployeeNumber  int(11) default NULL,
   creditLimit  double default NULL,
  PRIMARY KEY  ( customerNumber )
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Edit:

What i prefer:

    CREATE DATABASE IF NOT EXISTS classic_models;
    USE  classic_models ;
    DROP TABLE IF EXISTS  customers ;
    CREATE TABLE  customers  (
       customer_number  int(11) NOT NULL,
       customer_name  varchar(50) NOT NULL,
       -- or i define the column name this way:
       name varchar(50) NOT NULL, -- NOT customerName and NOT customer_name
       PRIMARY KEY  ( customer_number )
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Am i right?

I recommend an article: sql convention from Faruk Ateş Do you have any advice for naming conventions here?

Answer 1

You're never right (or wrong) about naming conventions. As you go from employer to employer, you'll encounter different conventions at every workplace, and you'll always have to adapt. You've already stated what you prefer, so when working on your own projects, simply use that, except your system is built on top of something else, that consistently uses another convention. Then I'd say you'd be better off using that convention in that project. Consistency > Preference.

Answer 2

both naming conventions you've shown are completely acceptable. writingLikeThis is easier to type, but writing_like_this is easier to read. The most important thing is consistency. Pick one naming convention and stick with it.

Answer 3

Please keep calm if you don't like this idea,
but did you consider column names independent from table names ?

Semantics

The semantic would be :
"if two fields are called startDate and endDate, then they designates the dates that determine the period considered for the current table."

That semantic usually crosses tables, so having a consistent name is good.

Implementation concerns

In the database

Maybe some people say that they still understand if that common column name is prefixed by the table name. But in several use cases, we got bitten by this and prefer:

• To you use meta-requests (ex. create a request to read all columns named startDate, or find all tables that use some reference data) efficiently, fixed names are much easier.
• Stored procedure or triggers also can be reused easier if the names are fixed.

ORM

ORM are really good at letting you define a field once, then create subclasses that will have that field automatically (composition is also used). The database has no subclassing, but

• If the various tables mapped on the classes use the same name for columns, everything is natural.
• Otherwise, you have to hand-code (or declare) the fact that the startDate in the code is implemented in the database as XXXStartDate or XXX_start_date...

Requiring Aliases

Some requests are self-joins, joining the same table twice, thus requiring to use aliases for table names in every case.

Most other hand-coded requests join several tables. This naming policy would increase the likelihood that two columns have the same name, so that would require to use aliases. Is that a problem? I think it isn't because:

1. Using aliases is often recommended anyway, and considered good-practice.
2. Using aliases in both cases allow for a more consistent coding environment.
3. Using aliases allow a few advantages over table names:
   a. Can allow long and clear table names, including a prefix to group the tables by "modules", as a shorter alias can be used in requests.
   b. While a table name is fixed for all modules of all applications that access the database, applications or modules can use varying aliases in their requests, allowing to provide more semantic in the requests (just like the choice of naming a variable in the code, with the same rules).