0

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.

Imnotapotato
  • 5,308
  • 13
  • 80
  • 147

2 Answers2

2

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Ok, and if one day I decide to set a NULLed cell to a date, If I decide to set it back to NULL, will this be a problem? (result any errors?) – Imnotapotato Jan 02 '19 at 13:55
  • NULLs are great to show a lack of data, for instance in cases where you do not know the expiration date of an item. However NULLs can be problematic if you index the column, – Dave Stokes Jan 02 '19 at 13:56
  • I can't answer because I don't know exactly what you have in mind. A MySQL datetime column can hold either `NULL` or a valid datetime value. – Tim Biegeleisen Jan 02 '19 at 13:56
  • @DaveStokes Why would `NULL` values pose a problem any more than some other value? Unless perhaps if the bulk of the column be `NULL`, in which case any index might not help. – Tim Biegeleisen Jan 02 '19 at 13:57
  • @TimBiegeleisen I just want to be sure this is a proper way to do it – Imnotapotato Jan 02 '19 at 14:29
  • @TimBiegeleisen Yes, if the majority or a large number of the rows are NULL it will make indexes less beneficial. – Dave Stokes Jan 02 '19 at 21:29
  • Just use `NULL`. When you have a million rows and _if_ you have issues, come back with a new Question. – Rick James Jan 03 '19 at 18:26
1

Use NULL if you don't know the exp_date yet and use 9999-12-31 (max supported value for date) if the row does not expire.

trashcandrummer
  • 36
  • 7