0

When using an interface like phpmyadmin/heidisql or others, I have this tables:

account
account_post
account_post_status
account_post_type
account_status
account_type

Ideal for me is this:

account
account_type
account_status
account_post
account_post_type
account_post_status

So I found that using numbers as prefix is just perfect. It does the job exactly as I want. I mean:

01_account
02_account_type
03_account_status
04_account_post
05_account_post_type
06_account_post_status
and so on

I read several subjects like mine, with users who want to prefix tables, but with obscure reasons. My reason is because I want to see them in an interface in a specific order. Tables listed above are just an example. I have a maximum of 40 tables in my database and order them using numbers is just fascinating.

I also read an sql naming convention for tables and I understand that prefixing table names with numbers is not a good practice at all. I found that some database systems doesn't even accept table names to be prefixed with numbers.

Anyway, for me, there is only one little disadvantage. The application itself. I am now using mysql with PHP. When i will use queries to select/insert/update etc from database, I should also use the prefix, which is quite annoying. But I will create a function to add the prefix from a known list (because I may want to change the order in the meantime). Example:

"SELECT id FROM ".add_table_prefix('account')." WHERE whatever"

I also have a convention for naming foreign keys, and these names includes the table names. I won't add the prefix there.

Also, I don't know if phpmyadmin or other GUIs for mysql have options to create a custom order for tables, but is not in my interest, as I want to have this order anywhere I access the database without other configurations.

I'm listening. How bad is what I'm doing ? There is one single guy here who have made this ?

Edit:

Be honest, in a database is more often to have a smaller number of columns in a table than tables in a database. In such conditions, give me a good reason why we would like to order columns (as we want, and we do) but we would not want to order the tables.

The only benefit of ordering columns in a table is for our view, right ? Why not doing this for tables too ? Just because the SQL language didn't implemented this (so GUIs), that doesn't mean it doesn't make sense to order tables, right ?

1
  • Or, instead of @danblack 's solution, you could add an order_by column and store the value in that - this has the benefit of being a bit more explicit and not coercing (using a column for something for which is was not intended).
    – Vérace
    Aug 18, 2021 at 16:11

1 Answer 1

7

Its (more than) a little insane. If you add a account_post_comment field you need to renumber and rework all of your code.

Better is if you use alter table (or CREATE TABLE to add a comment and use this).

ALTER TABLE account_post COMMENT '03'

To retrieve in order use the information_schema.tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT
FROM information_schema.tables
WHERE table_schema = 'db_name'
ORDER BY TABLE_COMMENT
3
  • 1
    Still need to renumber many tables if you create a new table, but at least changing a comment is a metadata-only change. Alternatively, use floating-point numbers in the COMMENT, so you can insert a lot of new tables before you need to renumber them. Aug 17, 2021 at 19:48
  • It's true. If I add a new table, I need to renumber the table names and to add the new table in the function add_table_prefix. That's not a hard job and is not a thing I will make every day.
    – Andrei
    Aug 17, 2021 at 20:17
  • As the query you gave and the advice to set the order in comments, this is useful just one time, and does nothing to do with a menu for showing tables, like phpmyadmin has. So that's not an good alternative...
    – Andrei
    Aug 17, 2021 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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