Column Captions in MySQL (in addition to column names)

Question

In MS Access (2003), a column can have both a "name" (required obviously) and a "caption" (optional). It can also have a "description" (optional) and an "input mask".

The main advantage is that the column name can be code friendy (eg lower case with underscores), while the caption can be user friendly (eg title case / proper case, with spaces, or with completely different words).

If a column has a caption, then it is used by default in forms.

[Q1] Can this be achieved in MySQL? I'm most interested in the captions, but would also welcome advise re column linked description and input mask. I haven't searched re input mask yet.

I'd prefer to use an approach which can be implemented in almost any SQL database. So, the less proprietary, less hacky, and more "standard SQL" (sic) the approach, the better.

I note that, in MySQL, a column can have a "comment". I could use these, and use code to extract them when required. But (1) this is inconvenient and hacky and (2) it seems to me that the comment should be used for another purpose (eg notes to self or other developers, or to advise re data entry).

I'm currently using phpMyAdmin (via XAMPP), but I used MySQL Workbench previously. I can't find any information about this on either of those sites or on Stack Overflow.

These keywords and links are related to the topic, but don't answer the question:

COLUMN_NAME
COLUMN_COMMENT

MS Access: setting table column Caption or Description in DDL?

MySQL query to get column names?

MySQL 5.7 Reference Manual :: 19 INFORMATION_SCHEMA Tables

Commenting your MySQL database... (yada yada)

Thanks in advance.

---

@Jim Garrison Thanks for replying. I didn't vote for that answer because it's not doing a lot of what Access does. Not your fault though - you're just the messenger! I'm not sure how one should thank someone for their effort in Stack Overflow, since the comment area says not to use it for that purpose. Cheers anyway for the information.

I had an idea, although it too is far from ideal.

I could create a separate table to hold supplemental metadata (captions/aliases, descriptions, input masks etc) about all other tables in the database.

Its structure would be:

id | table_name | column_name | friendly_alias | description | input_mask

Unfortunately, it would be a chore to maintain, unless there would be a way of automatically populating it with table names and column names, and updating these values automatically if they were renamed, inserted or deleted.

It really would be much better if it were an extension of the built in information schema table(s) though, with null values allowed for those optional fields.

Could it be a separate table, but with a 1:1 relationship with the built in information schema table(s)?

Bear in mind that I'm an amateur enthusiast by the way!

Answer 1

There's nothing builtin that is easily usable without metadata queries. However, you can use column aliases and name-quoting to get whatever name you want, as in

select column_1 as `Date of Birth`,
       column_2 as `Home Address`,
       etc.

Answer 2

MySQL does allow comments, as you've noted, but you can't use them directly for queries. phpMyAdmin does show them, so if you're using phpMyAdmin that is the best solution/workaround.

However, I think you're over complicating things. This isn't the same as Access in that forms and labels are not automatically created as part of MySQL when you create a database/table. You've got to then write code (in whatever programming language you wish) to interact with that database. When you're writing code, especially standard SQL, you don't want to refer to table names like "address of the buyer", you'll want "address" because, well, that's how programming works and we programmers don't want to have to type the same long variable name again and again. And many systems choke on spaces. So in your application, you can display to the user "Please enter here the permanent shipping address of the buyer using the standard address scheme of their home country so that it's accepted by the postal service with minimal hassle", but there's no way you'd use that as a variable name. I hope this makes sense and isn't insulting; I'm just trying to demonstrate that your table names don't really correspond to anything the user sees. Access is a bit different because the program tries to make it easy for you to create a database structure and then quickly edit the form a user will use to insert or modify data. Therefore it makes a bit more sense to be able to provide a comment that it uses whenever referring to that field.

Anyway, you asked about keeping most to standard SQL, and the concept of referring to a table by a comment is nonstandard, so in the interest of being able to implement it in any database, I'd suggest using the table name in your queries.

Hope this helps a bit.