Is there a naming convention for MySQL?
MysqlNaming ConventionsMysql WorkbenchMysql Problem Overview
Here's how I do it:
- Table names are lower case, uses underscores to separate words, and are singular (e.g.
foo
,foo_bar
, etc. - I generally (not always) have a auto increment PK. I use the following convention:
tablename_id
(e.g.foo_id
,foo_bar_id
, etc.). - When a table contains a column that is a foreign key, I just copy the column name of that key from whatever table it came from. For example, say table
foo_bar
has the FKfoo_id
(wherefoo_id
is the PK offoo
). - When defining FKs to enforce referential integrity, I use the following:
tablename_fk_columnname
(e.g. furthering example 3, it would befoo_bar_foo_id
). Since this is a table name/column name combination, it is guaranteed to be unique within the database. - I order the columns like this: PKs, FKs, then the rest of columns alphabetically
Is there a better, more standard way to do this?
Mysql Solutions
Solution 1 - Mysql
I would say that first and foremost: be consistent.
I reckon you are almost there with the conventions that you have outlined in your question. A couple of comments though:
Points 1 and 2 are good I reckon.
Point 3 - sadly this is not always possible. Think about how you would cope with a single table foo_bar
that has columns foo_id
and another_foo_id
both of which reference the foo
table foo_id
column. You might want to consider how to deal with this. This is a bit of a corner case though!
Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.
Point 5 - I would avoid this. It provides you with little and will become a headache when you want to add or remove columns from a table at a later date.
Some other points are:
Index Naming Conventions
You may wish to introduce a naming convention for indexes - this will be a great help for any database metadata work that you might want to carry out. For example you might just want to call an index foo_bar_idx1
or foo_idx1
- totally up to you but worth considering.
Singular vs Plural Column Names
It might be a good idea to address the thorny issue of plural vs single in your column names as well as your table name(s). This subject often causes big debates in the DB community. I would stick with singular forms for both table names and columns. There. I've said it.
The main thing here is of course consistency!
Solution 2 - Mysql
Consistency is the key to any naming standard. As long as it's logical and consistent, you're 99% there.
The standard itself is very much personal preference - so if you like your standard, then run with it.
To answer your question outright - no, MySQL doesn't have a preferred naming convention/standard, so rolling your own is fine (and yours seems logical).
Solution 3 - Mysql
MySQL has a short description of their more or less strict rules:
https://dev.mysql.com/doc/internals/en/coding-style.html
Most common codingstyle for MySQL by Simon Holywell:
See also this question: https://stackoverflow.com/questions/5951245/are-there-any-published-coding-style-guidelines-for-sql/40134564#40134564
Solution 4 - Mysql
Thankfully, PHP developers aren't "Camel case bigots" like some development communities I know.
Your conventions sound fine.
Just so long as they're a) simple, and b) consistent - I don't see any problems :)
PS: Personally, I think 5) is overkill...
Solution 5 - Mysql
Simple Answer: NO
Well, at least a naming convention as such encouraged by Oracle or community, no, however, basically you have to be aware of following the rules and limits for identifiers, such as indicated in MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
About the naming convention you follow, I think it is ok, just the number 5 is a little bit unnecesary, I think most visual tools for managing databases offer a option for sorting column names (I use DBeaver, and it have it), so if the purpouse is having a nice visual presentation of your table you can use this option I mention.
By personal experience, I would recommed this:
- Use lower case. This almost ensures interoperability when you migrate your databases from one server to another. Sometimes the
lower_case_table_names
is not correctly configured and your server start throwing errors just by simply unrecognizing your camelCase or PascalCase standard (case sensitivity problem). - Short names. Simple and clear. The most easy and fast is identify your table or columns, the better. Trust me, when you make a lot of different queries in a short amount of time is better having all simple to write (and read).
- Avoid prefixes. Unless you are using the same database for tables of different applications, don't use prefixes. This only add more verbosity to your queries. There are situations when this could be useful, for example, when you want to indentify primary keys and foreign keys, that usually table names are used as prefix for id columns.
- Use underscores for separating words. If you still want to use more than one word for naming a table, column, etc., so use underscores for separating_the_words, this helps for legibility (your eyes and your stressed brain are going to thank you).
- Be consistent. Once you have your own standard, follow it. Don´t be the person that create the rules and is the first who breaking them, that is shameful.
And what about the "Plural vs Singular" naming? Well, this is most a situation of personal preferences. In my case I try to use plural names for tables because I think a table as a collection of elements or a package containig elements, so a plural name make sense for me; and singular names for columns because I see columns as attributes that describe singularly to those table elements.
Solution 6 - Mysql
Consistency is what everyone strongly suggest, the rest is upto you as long as it works.
For beginners its easy to get carried away and we name whatever we want at that time. This make sense at that point but a headache later.
foo
foobar
or foo_bar
is great.
We name our table straight forward as much as possible and only use underscore if they are two different words. studentregistration
to student_registration
like @Zbyszek says, having a simple id
is more than enough for the auto-increment. The simplier the better. Why do you need foo_id
? We had the same problem early on, we named all our columns with the table prefix. like foo_id
, foo_name
, foo_age
. We dropped the tablename now and kept only the col as short as possible.
Since we are using just an id for PK we will be using foo_bar_fk
(tablename is unique, folowed by the unique PK, followed by the _fk
) as foreign key. We don't add id
to the col name because it is said that the name 'id' is always the PK of the given table. So we have just the tablename and the _fk
at the end.
For constrains we remove all underscores and join with camelCase (tablename + Colname + Fk) foobarUsernameFk
(for username_fk col). It's just a way we are following. We keep a documentation for every names structures.
When keeping the col name short, we should also keep an eye on the RESTRICTED names.
+------------------------------------+
| foobar |
+------------------------------------+
| id (PK for the current table) |
| username_fk (PK of username table) |
| location (other column) |
| tel (other column) |
+------------------------------------+
Solution 7 - Mysql
as @fabrizio-valencia said use lower case. in windows if you export mysql database (phpmyadmin) the tables name will converted to lower case and this lead to all sort of problems. see https://stackoverflow.com/questions/6134006/are-table-names-in-mysql-case-sensitive