Is there a naming convention for MySQL

Question

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 FK foo id  where foo id is the PK of foo   When defining FKs to enforce referential integrity  I use the following  tablename fk columnname  e g  furthering example 3  it would be foo 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

User · Accepted Answer

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

User · Answer

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 Are table names in MySQL case sensitive

User · Answer

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   http   www sqlstyle guide   See also this question  Are there any published coding style guidelines for SQL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot Plural vs Singular quot  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

[mysql] Is there a naming convention for MySQL?

Examples related to mysql

Examples related to naming-conventions

Examples related to mysql-workbench