3

In MySQL Workbench 8.0, I named the table such as Smartphone Brand.

But it automatically named in smartphonebrand after created which is very annoying and hard to find the name without the uppercase letter or whitespace character.

Anyone have any naming convention for schema and table in MySQL?

Huy
  • 191
  • 6
  • 18
  • 1
    *"Anyone have any naming convention for database and table in MySQL?"* it's more or less this [Schema Object Names](https://dev.mysql.com/doc/refman/8.0/en/identifiers.html) – Raymond Nijland May 24 '19 at 14:44
  • 4
    ideally `Smartphone Brand` should be `smartphone_brand` as the spaces would require using backticks every time when you use it like `\`Smartphone Brand\`` which is very annoying also.. But yes matter of opinion or taste i guess – Raymond Nijland May 24 '19 at 14:48
  • 4
    https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html " it is best to adopt a consistent convention, such as **always** creating and referring to databases and tables using **lowercase** names. This convention is recommended for maximum portability and ease of use." – spencer7593 May 24 '19 at 14:49
  • 2
    to add to @spencer7593 's comment i use lowercase, replace spaces with underscores and i tend to avoid using utf8 characters as the plague in database, table and columns names for me easy as i use english when programming [this](https://dev.mysql.com/doc/refman/8.0/en/identifier-mapping.html) is one off the reasons why i avoid using utf8 characters in database, table and columns names – Raymond Nijland May 24 '19 at 14:55
  • The MySQL database engine itself uses "case sensitive" identifiers, so upper/lower/mixed case names shouldn't be an issue at all. It must be an configuration issue with the MySQL Workbench tool. In any case, the use of spaces in the name will produce the annoying back ticks. I would suggest to use underscores (_) intead of spaces. – The Impaler May 24 '19 at 15:07
  • Possible duplicate of [Is there a naming convention for MySQL?](https://stackoverflow.com/questions/7899200/is-there-a-naming-convention-for-mysql) – Martin May 24 '19 at 15:18
  • 1
    @TheImpaler: it's not entirely true about MySQL database using "case sensitive" identifiers. This may hold with specific settings of `--lower-case-table-names` system variable on Linux and specific storage engines, but according to MySQL Reference Manual "case sensitivity of the underlying operating system plays a part". – spencer7593 May 24 '19 at 15:18
  • @spencer7593 Thanks, duly noted. I have used MySQL in linux only, and didn't know it could be different in Windows. I would guess it's case sensitive in MacOS too since it's unix based. – The Impaler May 24 '19 at 18:57

1 Answers1

4

Use lowercase (a-z), digits (0-9) and the underscore character (_) for table names.

This convention provides optimal usability and portability. This is documented in the MySQL Reference Manual here:

https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

Excerpt:

To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

An entity identified as "Smartphone Brand" can be implemented as tablename smartphone_brand.

Another convention I tend to follow is to name tables in the singular; naming what one row in the table represents. Why I would not name the table smartphone_brands.

Also use full words and avoid abbreviations. Also avoid prefixing/suffixing table names with object type identifier such as tbl_smartphone_brand or smartphone_brand_table.

--

Of course its possible to produce a successful software project following other conventions. The conventions I follow are just one possible pattern.

As an industry professional, once we run into significant problems with the other database identifier conventions (as in what should be a simple port of a database application from Windows to Linux turns into a laborious grind), which could have been avoided by following a different convention, we discover that those features that we might currently identify as minor annoyances turn out to be tremendous time savers.

spencer7593
  • 106,611
  • 15
  • 112
  • 140