0

I am refactoring a very old DB (< 2001) migrating from MyISAM to InnoDB and I experience some issues with the CamelCase notation. I don't know why but with the use of MySQL Workbench, sometime, my schemas are renamed in lowercase.

Anyway, the tables names in this DB are sometime pretty odd so I would like to rename them. As I am using the Illuminate\Eloquent ORM, it would be pretty convenient to use plurals for tables.

So I am proposing these kind of changes:

+-------------------+--------------------------+
| Before            | After                    |
+-------------------+--------------------------+
| Retailer          | suppliers                |
| RetailerComment   | suppliers_comments       |
| Category2Retailer | suppliers_has_categories |
+-------------------+--------------------------+

The question is:

Is it correct (acceptable), to name the table that holds the comments made on suppliers: suppliers_comments, both plurals? I could have named the table simply comments, but then it would have interfered with other tables.

I know this question could be primary opinion based, but I am rather looking for the largest consensus using as base of thoughts:

Any objective advice? I am not a MySQL/DB expert...

7
  • What is the MySQL version(x,y,z)? And what is the O.S Environment? Dec 25 '18 at 11:39
  • Linux, MySQL 8.0.13
    – nowox
    Dec 25 '18 at 11:40
  • But I am using MySQL Workbench from Windows 10 + WSL
    – nowox
    Dec 25 '18 at 11:40
  • 1
    As you said you are using the Workbench in windows 10 & accessing the MySQL database via remote host , which has been installed in Linux. Dec 25 '18 at 11:48
  • TBH, use whatever you find most comfortable. It’s your code base 😀
    – Philᵀᴹ
    Dec 25 '18 at 12:23
1

Is it correct (acceptable), to name the table that holds the comments made on suppliers: suppliers_comments, both plurals? I could have named the table simply comments, but then it would have interfered with other tables.

You can, But before RENAME of any tables name stop here & see the MySQL blog documentation here Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table.

MySQL RENAME TABLE examples

First, we create a new database named hr that consists of two tables: employees and departments.

To create the hr database

CREATE DATABASE IF NOT EXISTS hr;

To create departments & employees tables

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    id int AUTO_INCREMENT primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    department_id int not null,
    FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

enter image description here

Second, we insert sample data into both employees and departments tables:

INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');



INSERT INTO employees(first_name,last_name,department_id) 
VALUES('John','Doe',1),
 ('Bush','Lily',2),
 ('David','Dave',3),
 ('Mary','Jane',4),
 ('Jonatha','Josh',5),
 ('Mateo','More',1);

Third, we review our data in the departments and employees tables:

SELECT 
    department_id, dept_name
FROM
    departments;

enter image description here

SELECT 
    id, first_name, last_name, department_id
FROM
    employees;

enter image description here

Renaming a table referenced by a view

If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.

For example, we create a view named v_employee_info based on the employees and departments tables as follows:

CREATE VIEW v_employee_info as
    SELECT 
        id, first_name, last_name, dept_name
    from
        employees
            inner join
        departments USING (department_id);

The views use the inner join clause to join departments and employees tables.

The following SELECT statement returns all data from the v_employee_info view.

SELECT 
    *
FROM
    v_employee_info;

enter image description here

Now we rename the employees to people table and query data from the v_employee_info view again.

RENAME TABLE employees TO people;

SELECT 
    *
FROM
    v_employee_info;

MySQL returns the following error message:

Error Code: 1356. View 'hr.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:

CHECK TABLE v_employee_info;

enter image description here

We need to manually change the v_employee_info view so that it refers to the people table instead of the employees table.

Renaming a table that referenced by a stored procedure

In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.

Note: Here I have taken all codes & pictures from the mysqltutorial.org to clarify the RENAME of table.

After edit about the Naming Conventions for MySQL

  • Case sensitivity is set when a server is installed. By default, the names of database objects are case sensitive on UNIX and not case sensitive on Windows. For example, the names CUSTOMER and Customer are different on a case-sensitive server.
  • Database names must be unique. For each user within a database, names of database objects must be unique across all users. For example, if a database contains a department table that User A created, no other user can create a department table in the same database.
  • A name cannot be a reserved word in MySQL unless you enclose the name in quotation marks. See the MySQL documentation for more information about reserved words.

As Mr. Max Vernon already said that here Database names are completely up to the person creating them. Having said that, it makes sense to use some kind of sane naming convention.

For further your ref here

2
  • Thanks for your answer, but I does not really answer my question. I know what is the cost of renaming my tables and I can do pretty well with regular expressions in both SQL and code-base. My question was about the naming conventions to use.
    – nowox
    Dec 25 '18 at 18:38
  • Mostly I have seen in production environment the mysql table name is in lower case letter. Dec 25 '18 at 18:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.