7

Correct me if I'm wrong, but my understanding is that, in MSSQL, sub-structures of a database like Views, Schemas and Tables can be referenced using object notation such as:

Database.Schema.Table.Column

Each of these objects I believe has their own properties.

I need to replicate the structure of an MSSQL DB in MySQL and I am unsure what is the best practice.

I am thinking about creating tables in MySQL with the following naming convention:

Database
|---SubStructureX.Table
|   |---Column_A
|   |---Column_B
|---SubStructureY.Table
|   |---Column_C
|   |---Column_D
|
|

Therefore a MySQL query could look like this:

SELECT Column_A, Column_B FROM SubStructureX.Table

In short, "SubstructureX.Table" is just a table name that contains a dot. I would be doing this for ease of use during replication of the MSSQL structure. I don't care if the things before and after the dot are not objects in MySQL.

Is this good MySQL practice?

Carlos Muñiz
  • 1,858
  • 9
  • 25
  • 29
  • Are you planning to create `schemas` in `MSSQL` ? – Pரதீப் Jan 31 '17 at 01:38
  • The MSSQL database is created already and it contains Schemas and Views. I am trying to replicate the structure in MySQL. I guess I could also ask does MySQL support Schemas and Views?, but I really have no use for those structures once I have the DB ported to MySQL. Let me know if that makes sense, I may not be getting the terminology correct. – Carlos Muñiz Jan 31 '17 at 01:43

2 Answers2

11

In MySQL? No, I would think that it's not good practice to use periods in table names at all. I would think that it's very bad practice. The dot is the reference operator in SQL. That means if you want to refer to a column using fully qualified notation, you do so like this:

SELECT Table.Column_A ...

Or, with backtick quoting:

SELECT `Table`.`Column_A` ...

Now, imagine if your table is named StructureX.Table. Just like with a space, you've got to quote that to escape it because you don't want MySQL to think the dot is an operator. That means your SQL has to look like this:

SELECT `StructureX.Table`.Column_A ...

Or, with backtick quoting:

SELECT `StructureX.Table`.`Column_A` ...

Doesn't that look like a syntax error to you? Like maybe it's supposed to be like this:

SELECT `StructureX`.`Table`.`Column_A` ...

This would be a nightmare to maintain and as a systems analyst I would hate any application or developer that inflicted this nomenclature on me. It makes me want to claw my eyes out.

Microsoft SQL Server is different because it supports multiple schemas within a single database, while MySQL treats schema as a synonym for database. In MS SQL Server, schemas are collections of objects, and you can use them to organize your tables, or apply security to tables as a group. The default schema is dbo, which is why you see that one listed so often. In MS SQL Server syntax, this:

SELECT [StructureX].[Table].[Column_A] ...

Means within the current database, the schema named StructureX, table named Table, and column name Column_A. MS SQL Server actually supports a four part name, with the fourth part being the database:

SELECT [MyDatabase].[StructureX].[Table].[Column_A] ...

Here, MyDatabase is the database name.

That same style works in MySQL, except you have to remember that schema and database are synonymous. So there, this:

SELECT `StructureX`.`Table`.`Column_A` ...

Would mean database StructureX, table Table, and column Column_A.

Community
  • 1
  • 1
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • This is where I was trying to go. Instead of using the dot, would it be good practice to use an underscore in the table name? I'm assuming so, but just want to know the opinions of others. So in MSSQL I would have DB.Schema.Table and I could create tables in MySQL as DB --> Schema_Table, where "Schema_Table" is just a run-of-the-mill MySQL table. – Carlos Muñiz Jan 31 '17 at 02:01
  • 1
    @Charlie Yes, an underscore would be perfectly fine. I work with several systems that use a short abbreviation for the area the table is related to at the start of the table (usually 2-4 characters) that help organize tables into groups. It makes it *much* easier to find associated tables. – Bacon Bits Jan 31 '17 at 03:44
0

I Can say yes:

But instead of using table name, make a table some alias like this,

select a.column1 from yourTable as a

Using table alias is a good practice.

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
  • The question is about the best practice for naming convention of MySQL tables, not querying tables. Therefore, using you query above as an example, would this be good practice: SELECT column1 FROM a.yourTable AS aTableAlias – Carlos Muñiz Jan 31 '17 at 01:51
  • Your question said using "dot(.)" as far as i know using "dot(.)" seems to be an aliases so with naming convention too. Its all up to you. – Vijunav Vastivch Jan 31 '17 at 01:55
  • I meant to ask using periods (.) as part of a table name, such as This.Is.One.Table – Carlos Muñiz Jan 31 '17 at 02:08
  • 1
    I can say no. Its not a good practice. It could allocate some memory space of creating a long table name but not necessarily needed. – Vijunav Vastivch Jan 31 '17 at 02:10