0

I'm creating tables in a database currently and one of the tables has a status column. the status column has 4 different statuses that it could potentially have. I've made a separate table that has table_statuses and links them together via id. But I'm wondering if I shouldn't just type in the varchar directly.

i.e I have

-- -----------------------------------------------------
-- Table `m_document_version_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version_status` (
  `document_version_type_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `m_document_version_status` (`value`) VALUES ("wip");
INSERT INTO `m_document_version_status` (`value`) VALUES ("scheduled");
INSERT INTO `m_document_version_status` (`value`) VALUES ("current");
INSERT INTO `m_document_version_status` (`value`) VALUES ("deprecated");

-- -----------------------------------------------------
-- Table `m_document_version`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version` (
  `document_version_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `version` varchar(12) NOT NULL,
  `major` int(4) NOT NULL DEFAULT 0,
  `minor` int(4) NOT NULL DEFAULT 0,
  `patch` int(4) NOT NULL DEFAULT 0,
  `document_type_id` int(1) NOT NULL DEFAULT 1,
  `status` int(1) NOT NULL DEFAULT 1,
  `created_date` DATETIME NOT NULL,
  `launch_date` DATETIME DEFAULT NULL,
  `deprecated_date` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

I'm wondering if I shouldn't just remove the version_status table and input the varchars directly for simplicity

2
  • 1
    Use ENUM datatype.
    – Akina
    Commented Feb 19, 2020 at 8:51
  • 1
    But remember - the value which is a string visually is numeric in storage. So avoid implicit convertions during your operations - the result may differ from one you need in.
    – Akina
    Commented Feb 19, 2020 at 9:07

2 Answers 2

1

These look, to me, like statuses in a workflow that you are creating.
As such, it is highly likely that these status values will change over time. Because of this, I would discount the use of an Enum. Changing an Enum requires you to change the table definition, which requires you (as DBA) to get involved. Adding a new status [row] into a regular table makes it very easy to add new statuses, potentially even being a part of your application and Users being able to do this for themselves! (Obviously, you'll have a constraint that prevents them from deleting statuses that are in use!)

Also, I would recommend this approach over using a simple, varchar column, if only for the sake of consistency. is it possible that someone (you?) could accidentally(?) enter a status of "WIP" instead of "wip" and what effect would that have on your application code?

0

There are many pros and cons of ENUM. Search this forum for debates, sometimes heated.

I'll take the pro-ENUM side for this one question...

status ENUM ("unk", "wip", "scheduled", "current", "deprecated") DEFAULT "unk" NOT NULL

Notes:

  • All references to status use human-readable values, like 'vip'.
  • Space-efficient (1 byte). This is better than the 4-byte INT you suggested. (cf TINYINT)
  • WHERE clauses are more efficient by not having to JOIN to the other table. In some cases this is minor; in some cases, it makes a huge difference.
  • Case folding is easily handled by specifying a collation for the column. (Probably the default provided by the table is ..._ci anyway.)
  • If there is a 5th case where you might want to say NULL, or N/A, etc, then I recommend putting that case first in the list. (I often use 'unk' or 'unknown'.
  • It is subtly better to have a default value than to use NULL.
  • Adding another status on the end is just a simple, non-blocking, ALTER. If the DBA and the programmer are not on speaking terms, you have bigger problems.
  • Cost of an "implicit conversion"? As I often say - expressions are far less important to performance than the cost of fetching rows.

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.