What should be an 'expiration date' column type when in some cases it doesn't have any value? (blank? null? 0000-00-00?)

Question

I have a table stating which items I am using in my main menu:

model | display_name | sub_menu | active

MYSQL:

CREATE TABLE `main_menu` (
  `model` varchar(255) NOT NULL COMMENT 'name of the model',
  `display_name` varchar(255) NOT NULL,
  `sub_menu` enum('Fruits','Vegtables','Grains','Breads','Snacks','Sweets') DEFAULT NULL,
  `active` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

example:

model   | display_name  | sub_menu  | active
---------------------------------------------
potato  | Potato        | Vegtables | 0
apple   | Apple         | Fruits    | 1
pumpkin | Pumpkin       | Vegtables | 1

• I want to add an expiration date to it ("exp_date").
• Not all rows have an expiration date.

What should be the type of exp_date? Obviously "Date".. but in cases that there is no exp_date for a certain row, what is considered a "good practice"? Inserting NULL? inserting 0000-00-0?

model   | display_name  | sub_menu  | active    | exp_date
--------------------------------------------------------------
potato  | Potato        | Vegtables | 0         | NULL
apple   | Apple         | Fruits    | 1         | NULL
pumpkin | Pumpkin       | Vegtables | 1         | 2019-03-01

Using MySQL 5.7 and might migrate to the new 8 version. I just want to make sure I'm doing this properly. I remember (not sure from where) that date types and date values in an issue and there is a proper way to do it.

Answer 1

I don't see anything wrong with using NULL as a placeholder when the expiration date be not known. Consider as an example that you want to find all records which have not yet expired. Treating NULL as not having yet expired, we can write the following query:

SELECT model, display_name, sub_menu, active, exp_date
FROM main_menu
WHERE exp_date > NOW() OR exp_date IS NULL;