MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use.
What is a naming convention?
In computer programming, a naming convention is a set of rules for choosing the character sequence to be used for identifiers that denote variables, types, functions, and other entities in source code and documentation.
General rules ā Naming conventions
UsingĀ lowercaseĀ will help speed typing, avoid mistakes as MYSQL isĀ case sensitive.
Space replaced with UnderscoreĀ ā Using space between words is not advised.
Numbers are not for names ā While naming, it is essential that it contains onlyĀ Alpha English alphabets.
Valid Names ā Names should be descriptive of the elements. i.e.Ā ā Self-explanatory and not more than 64 characters.
Name can be singular or plural but as the database represents a single database it should be singular.
Avoid prefix if possible.
MySQL is usually hosted in a Linux server which is case-sensitive hence to stay on the safe side use lowercase. Many PHP or similar programming frameworks, auto-detect or auto-generate class-based table names and most of them expect lowercase names.
The table is made up of fields and rows filled with various forms of data, similarly the table name could be plural but the table itself is a single entity hence it is odd and confusing. Hence use names like User, Comment.
The table usually has the database or project name. sometimes some tables may exist under the same name in the database to avoid replacing this, you can use prefixes. Essentially, names should be meaningful and self-explanatory. If you canāt avoid prefix you can fix it using php class.
Use all above cases which includeĀ lowercase, no space, no numbers, and avoid prefix.
ChooseĀ short namesĀ no-longer than two words.
Field names should beĀ easy and understandable
Primary key can beĀ idĀ orĀ table name_idĀ or it can beĀ a self-explanatory name.
Avoid using reserve words as field name. i.e. ā Pre-defined words or Keywords. You can add prefix to these names to make it understandable like user_name, signup_date.
Avoid using column with same name as table name. This can cause confusion while writing query.
Avoid abbreviated, concatenated, or acronym-based names.
Do define a foreign key on database schema.
Foreign key column must have a table name with their primary key.
e.g. blog_id represents foreign key id from table blog.
Avoid semantically ā meaningful primary key names. A classic design mistake is creating a table with primary key that has actual meaning like ānameā as primary key. In this case if someone changes their name then the relationship with the other tables will be affected and the name can be repetitive losing its uniqueness.
Make your table and database names simple yet understandable by both database designers and programmers. It should things that might cause confusion, issues with linking tables to one another. And finally, it should be readable for programming language or the framework that is implemented.
