Search code examples
databasenaming

Column name for success and failure

I have a table request_track that saves various security related requests.

Unsuccessful and successful logins have to be saved each and the entry in the request_track table marked as such. Now I struggle to find a good name (for #2) and wanted to know what you guys would do at my place.

I thought about the following options:

Option 1

Make two TINYINT columns: is_login_success and is_login_failure default 0.
Pros

  • It is very clear
  • Handy to work with later when retrieving the data. To compare the failures and successes I can simply use sum(is_login_success) and sum(is_login_failure)

Cons

  • In the logic only one of the two will be set with 1 or both with 0. It is technically possible though to set both with 1 which would cause unpredictable issues in the code.
  • 2 columns when only one could be used to have a similar effect

Option 2

Something like one is_login_success TINYINT default null.
1 would indicate that it was a successful login request, 0 would mean unsuccessful request and null means it's neither. Pros

  • Only one column is needed

Cons

  • It is not clear at all that 0 means explicitly that it's a failure.
  • To retrieve the data to compare all successes and failures two queries are needed

Option 3

I could make a is_login ENUM('success', 'failure') default null
Pros

  • Very clear
  • Only one column

Cons

  • To retrieve the data to compare all successes and failures two queries are needed (or is there a way to sum the failures and successes and return both with enum?).

My favourite is clearly #3, but I don't want to use two SELECT queries. I'm sure though it's possible in only one. I should have paid more attention in class.

Solution

  • You don't need two queries to fetch the number of successes/failures for any of these cases.

    Option 1

    Use the SUM function once for each column.

    CREATE TABLE request_track (
  request_id INT NOT NULL AUTO_INCREMENT,
  is_login_success TINYINT NOT NULL,
  is_login_failure TINYINT NOT NULL,
  PRIMARY KEY ( request_id )
);

    SELECT SUM( is_login_success ) AS num_success,
  SUM( is_login_failure ) AS num_failure
FROM request_track;
    num_success num_failure
    13 2

    Option 2

    Use the SUM function once for each possible value. Use the CASE function to determine the value to use for each row.

    CREATE TABLE request_track (
  request_id INT NOT NULL AUTO_INCREMENT,
  is_login_success TINYINT,
  PRIMARY KEY ( request_id )
);

    SELECT SUM(CASE WHEN is_login_success = 1 THEN 1 ELSE 0 END) AS num_success,
  SUM(CASE WHEN is_login_success = 0 THEN 1 ELSE 0 END) AS num_failure,
  SUM(CASE WHEN is_login_success IS NULL THEN 1 ELSE 0 END) AS num_unknown
FROM request_track;
    num_success num_failure num_unknown
    13 2 3

    Option 3

    Same approach as option 2.

    CREATE TABLE request_track (
  request_id INT NOT NULL AUTO_INCREMENT,
  is_login ENUM('success', 'failure') DEFAULT NULL,
  PRIMARY KEY ( request_id )
);

    SELECT SUM(CASE WHEN is_login = 'success' THEN 1 ELSE 0 END) AS num_success,
  SUM(CASE WHEN is_login = 'failure' THEN 1 ELSE 0 END) AS num_failure,
  SUM(CASE WHEN is_login IS NULL THEN 1 ELSE 0 END) AS num_unknown
FROM request_track;
    num_success num_failure num_unknown
    13 2 3

    Regardless, your approach should consider

    1. How easy is it to maintain the data?

      As you point out, the first option has the potential for invalid data to get into your table. If a row ends up with 1 in both is_login_success and is_login_failure then you've lost data.

      The second option also has the possibility to lose data. What happens if a query inserts 2 into is_login_success? Without a constraint, the database won't mind, but the row won't make any business sense.

      The third option prevents both of these mistakes. There is only one column so a given row cannot be in conflicting states. The column has a pre-determined number of possible values so it cannot be in an invalid state.

    2. How easy is it to maintain the table?

      Adding a single column to each row is not complicated, but you mention that this table will be used to track different types of requests. Will you need a new is_<request type> column for each type? Your table has the potential to become quite wide quite quickly.

      You might consider cutting each row down to an identifier, a type, and a status. The meaning of the status is allowed to differ between types and can be defined in an external table. When you need to add or remove a type, you are adding or removing an external table instead of adding or removing columns on your table.