[wp-trac] [WordPress Trac] #55942: Add a "type" field to the meta and options tables

[WordPress Trac]

#55942: Add a "type" field to the meta and options tables
-------------------------+---------------------
 Reporter:  azaozz       |       Owner:  (none)
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  6.1
Component:  Database     |     Version:
 Severity:  normal       |  Resolution:
 Keywords:  2nd-opinion  |     Focuses:
-------------------------+---------------------
Changes (by azaozz):

 * keywords:   => 2nd-opinion


Comment:

 Yes, ENUM seems best. Just wondering if we should implement the MySQL
 built-in `ENUM()` or do it "by hand" by adding a TINYINT and mapping the
 index numbers with PHP constants.

 Advantages of using ENUM:
 - Wouldn't need additional processing, like mapping index numbers to names
 ('int', 'float', 'string', etc.). No need of PHP constants to help there.
 - Better/readable display when looking directly in the DB (like with
 phpMyAdmin).
 - Pretty straightforward to add.

 Main disadvantages of using ENUM:
 - In addition to the defined values there always are `''` (mapped to `0`)
 and `NULL`. That may be somewhat confusing.
 - A bit tricky `ORDER BY`. Sorting is by index, not by name. That index
 depends on the order in which the values were defined. Need to use `CAST(
 ... as CHAR )` to sort by name.
 - Adding more values would require altering the tables.
 - Harder to list all possible values, need to look at
 INFORMATION_SCHEMA.COLUMNS.
 - Perhaps not particularly relevant but ENUM is a MySQL extension, not a
 standard ANSI SQL.

 Re-reading the above, I'm 50/50 which method would be better. Not sure if
 the drawbacks of using ENUM are that significant. On the other hand using
 TINYINT and keeping the mapping outside of the DB gives us full control,
 even plugins would be able to add to that mapping, eventually.

 So, what say you? To ENUM or not to ENUM? :)

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/55942#comment:6>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform