2

As status and type is reserved key in MySQL, I need a column name similar to status to identify the status of a row.

I know there is way to ignore this problem but in different language and different framework it suddenly arise this problem in different way, so I need to ignore this word and need a similar word that sound almost same and can be understandable from the name.

The potential values for my use case are true/false. What do you suggest to name a Boolean column that defines the status of that row?

Here status is defining that row if it is active or inactive. And not every where I'm using status column for this purpose, Somewhere status column is defining if the status is pending, current, confirmed, applied, rejected, etc. Like this shorts of key.

  • 1
    So then call it in this context is_active and set it to true or false. Not just does that make more sense, it provides a sexy syntax SELECT * FROM t WHERE is_active If it means means pending, create is_pending or is_current status only makes sense over an enumeration of discrete values SELECT * FROM listings WHERE status IN ( 'sold', 'terminated', 'active' ); etc. In that case, I would go with @Joe W's suggestion listing_status -- from a DBA's perspective Joe is right. – Evan Carroll Oct 2 '17 at 15:18
5
+50

Community wiki answer:

status

  • condition
  • situation
  • state

type

  • kind
  • sort
  • variety
  • category

Not clear to me if you are looking for synonyms. If so, and you need mere synonyms in the future, check out the word-choice tag on English Language & Usage Stack Exchange.

You might need to be careful with column names like these as they might confuse a user, since they can have a different meaning than row status or record type.

6

As a_horse_with_no_name commented:

I typically include the entities name with that, e.g. person_status or element_type. That also makes reading queries easier (at least in my eyes)

...it is better to include more information with those column names in order to make them more easily understandable.

For example you in a user table you could have a user_status and user_type column and those names would clearly define exactly what you are referring to with status and type. It would make it even more useful if those columns where using reference tables such as user_status and user_type.

Those are just examples. The table name could be modified to indicate that it is a reference table such as r_user_status or user_status_ri. Or one can work on making it more generic (but still more descriptive than status), so that it can be used for more tables. This would also allow you to remove repeated data that might need updating, and move the update to a single table for when you might need to change the text of an existing status.

In the end avoiding generic names such as status and type will make it much easier for developers who join the project later on to easily understand the data and its relationships.

2

Append underscore

The SQL standard explicitly promises to never use a trailing underscore on any keyword or reserved word.

So name your columns status_ and type_.

I name all my tables and columns in this fashion so I never have to worry about collisions with any of the thousand words reserved by various databases. I discovered a beneficial side-effect: eliminating some ambiguity. In discussions, emails, and app programming, it is now obvious when we are referring specifically to a database table or column such as customer_ versus the concept of a customer or a variable named customer.

I cannot quote the SQL spec because it is copyright protected, unfortunately.

  • In the SQL:2011 spec, read section 5.4 Names and identifiers under the heading Syntax Rules item 3, NOTE 111.
  • In SQL-92 see section 5.2, item 11. Just searching for the word underscore will work.
  • It's really a good idea. But still want some exact names :) – M.A.K. Ripon Sep 21 '17 at 9:39
  • 2
    I believe that quoting (small parts of a) work for illustratve purposes is allowed! " Note: It is the intention that no <key word> specified in this International Standard or revisions thereto shall end with an <underscore>." – Vérace Sep 21 '17 at 16:19
  • 1
    The specs you linked have both been withdrawn. in the SQL-92 spec which you can get to from the wiki link it states for intermediate sql no identifier body shall end in an underscore, in fact looking at the generic examples you will never see an underscore at the end of a name and only at the beginning. Also should be noted that if the specs say something isn't going to be used for the official parts there is probably a good reason that they should not be used. When looking at your column names it is hard to determine if they are incomplete or not. – Joe W Sep 21 '17 at 16:24
  • 1
    @JoeW Again, your are conflating instructions to those people who are implementing SQL with those people using SQL. The spec says <key word> shall end in an underscore. They are talking to the creators of Oracle and Postgres, not us app developers and DBAs. So, yes, that means it is perfectly acceptable to name your own identifiers including column names with a trailing underscore. I do it all the time in multiple SQL implementations. Works quite well. – Basil Bourque Sep 21 '17 at 20:57
  • 2
    From the SQL-92 standard in the wiki article you linked. "The following restrictions apply for Intermediate SQL: No <identifier body> shall end in an <underscore>." Which to me sounds pretty clear that you don't end identifiers with an underscore regardless of who is creating them. contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt – Joe W Sep 21 '17 at 21:14
1

Try to avoid it like the plague.

  • As applicable to you, and for binary conditions, use something more descriptive like is_active and set it to true or false. Not just does that make more sense, it provides a sexy syntax SELECT * FROM t WHERE is_active. If it means pending, create is_pending or is_current.
  • For an enumeration of discrete values such as SELECT * FROM listings WHERE status IN ( 'sold', 'terminated', 'active' ); etc. In that case, I would go with @Joe W's suggestion on the name of listing_status. There is one addendum here, if this is what you need you should look into the ENUM type.
  • For a list so extensive or dynamic that isn't possible, I would name it listing_status_id and link out to a foreign table listing_status. This is exactly what @Joe W. suggests

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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