Home Naming Conventions in MySQL

In this course, we expect you to use the MySQL version of SQL. There are different naming conventions in SQL, but for consistency we ask you to follow our course-specific coding guidelines.

As with any other language, names should be descriptive and distinguishable. In SQL, this particularly applies to database, table, and attribute names (alias conventions are an exception to this rule, as discussed later in the Alias Usage and Naming Conventions section).

Letter-casing Conventions

SQL commands, datatypes, and constraints (e.g. DEFAULT, PRIMARY KEY, AUTO INCREMENT, etc.) should be in ALLCAPS. It is legal to write commands in lowercase, but this often leads to subtle bugs with lowercase attribute/column names, so we expect you to use the ALLCAPS convention for any commands or datatypes.

Database names (e.g. c9 or imdb) should be lowercased (no numbers or special characters lie "_" or "-").

SQL Table Letter-casing Conventions

For table names, you may use PascalCasing (camelCasing with the first letter also capitalized) or lowercasing conventions, as long as you are consistent. In either case, table names should only contain alphabetic characters (no "_" or "-").

When creating a new table, you shoud use lowercase_underscore conventions for multi-word column names (no capitalization).

create table dogBreeds(
  breedID int,
  breedName varchar(20),
  PRIMARY KEY(breedID)
);
       
select breedName
from dogBreeds
where breedName like 'great%';

CREATE TABLE DogBreeds(
  breed_id INT,
  breed_name VARCHAR(20),
  PRIMARY KEY (breed_id)
);
 
SELECT breed_name
FROM DogBreeds
WHERE breed_name LIKE 'great%';

Alias Usage and Naming Conventions

When writing multi-table queries, it is often helpful to use a short-hand name to avoid conflicts between two instances of the same table. As a declarative language, SQL statements tend to be relatively concise and easy to interpret with English-like command names. In this case, short alias names tend not to introduce much confusion, as long as they follow a consistent convention.

You will often find aliasing helpful for queries that reference multiple instances of the same table or when you are working with more than one instance of tables that share a column name (there may be a case where two different tables share an identical column name). Aliasing may also be used to improve readability even when it is not needed (e.g. there are no column name conflicts but you are referencing a long table name in many places) but most short queries do not include aliasing.

When naming your table aliases, you may use single-letter names using the first letter of the table name, and may distinguish two aliases for the same table name using a numerical suffix. If two different tables share the same first letter, you may break ties with the second letter, or you may use a substring of the name that is reasonably distinguishable.

The "bad example" below does not clearly distinguish between the countries and cities tables when it names all with the c# convention (# being the number appended to each). The first "good example" is an accepted alternative since it is more clear that c and ci are different tables (note that it is a little easier to follow what the "good example" is selecting). Alternatively, you could omit the alias for the countries table since it is only used once, but because both countries and cities share a column called "name" (e.g. "Italy" is a name of a country and "Milan" is a name of a city) you need to specify which table you are referencing in the first line for it to be valid SQL. The second "good example" demonstrates a good use of this convention.

SELECT DISTINCT c1.name
FROM countries c1, cities c2, cities c3
WHERE c3.country_code = c1.code AND c3.country_code = c1.code
  AND c2.population >= 5000000 AND c3.population >= 5000000 
  AND c2.id < c3.id;

-- example 1, with alias for single countries table
SELECT DISTINCT c.name
FROM countries c, cities ci1, cities ci2
WHERE ci1.country_code = c.code AND ci2.country_code = c.code
  AND ci1.population >= 5000000 AND ci2.population >= 5000000 
  AND ci1.id < ci2.id;

-- example 2, without alias for single countries table
SELECT DISTINCT countries.name
FROM countries, cities ci1, cities ci2
WHERE ci1.country_code = countries.code AND ci2.country_code = countries.code
  AND ci1.population >= 5000000 AND ci2.population >= 5000000 
  AND ci1.id < ci2.id;