MySql column value to indicate 2 possible options

Question

I don't know if the title represent my doubt very well, but basically I have some columns that can represent 2 possible values.

For example, with the following table that retains data about orders:

id | payment_type | shipping_type
14 | 1 | 1
15 | 1 | 2
16 | 2 | 1 

So, for this those 1 and 2* would be:

• payment_type: 1= 'Money', 2= 'Online'
• shipping_type: 1= 'Delivery', 2= 'Take on store'

_{* The number values are used to store in the database only, on the screen it's displayed the text for the admins.}

This data need to be displayed on the screen for the admins to analyse the order report. Some times the "labels" (money/online) may change, not so frequently but that's a possibility, but it will always be one or the other option.

This data should be displayed on the screen as simple as the following:

Order: #01234
Payment type: Money
Delivery type: Take on store

This data also needs to be able to be edit. For example, let's say I sold an Item for my client to delivery on his address, but for some reason he was passing by and decided to stop on the store to take it. So I need to change the shipping_type from 1 _(delivery) to 2 _{(take on store)}. Same applies for the payment_type.

---

So, my doubt is about the way it's organized and how I can manage this data in order to be able display and edit as needed. Currently I'm naming the fields manually with a CASE statement, ex.: CASE WHEN payment_type = 1 THEN 'Money' ELSE 'Online' END AS 'payment_type'. But I'm feeling this is not the best way to go, since it's very manually and if I update the field, I need to also update it manually in multiple queries. If the terminology changes, ex.: Money becomes Check, than I would need to go trought all the options and change it. The name or "labels", don't change often, but it's something that may occur, as it already did in the past.

As an alternative, I was thinking about a table like the one shown below:

payment_table
id | value | name
- | 1 | Money
- | 2 | Online

But would have a lot of these tables. Actually, it's not a big deal, but I'd like to know if this is the correct approach to this situation. I'm new to databases and backend in general.

• Sudipta Mondal: This is what I do when I have such scenarios, create a Property table, with columns property_type, key, value. I however use this generally in web application, where there is not much data involved.
• CelsomTrindade: @SudiptaMondal would you mind elaborating a proper answer?

Answer 1

You can use the MySQL ENUM type which is stored as a one-byte int internally. From the docs which only hint on implementation,

Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.

Though it's documented elsewhere explicitly,

1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

MySQL can use two byte enums if you have 255<x<65,535 potential values to enumerate over.

It looks like this,

CREATE TABLE payment (
 id int AUTO_INCREMENT,
 payment ENUM('money', 'online'),
 PRIMARY KEY (id)
);

• Rick James: And declare it NOT NULL. (Add another enum option if you need something such as 'unknown'.)