MySQL Storing "Generic" Data (Can be very long or very short)

Question

ok i am developing a quiz ... the question's options ... so i am wondering what can i use for options that can both be very long (eg. Long paragraph of text/code) or very short (eg. boolean)

if i use whether i use VARCHAR(65535) or TEXT i only use the space i need right, L chars + 1 or 2 bytes vs L chars + 2 bytes? any speed differences? or any other concerns?

what other ways might i work around this - options that maybe very long or very short that maybe supposed to ideally use different data types

Answer 1

I think you should have multiple tables, one for each answer type. Adding a parent table would give you common fields:

CREATE TABLE answer (
    id INT PRIMARY KEY,
    question_id INT
    type ENUM('bool', 'string'),
    FOREIGN KEY question_ref (question_id) REFERENCES question(id)
) Engine=innodb;

CREATE TABLE answer_bool (
    id INT,
    value TINYINT NOT NULL,
    FOREIGN KEY parent_key (id) REFERENCES answer(id);
) Engine=innodb;

CREATE TABLE answer_bool (
    id INT,
    value TEXT NOT NULL,
    FOREIGN KEY parent_key (id) REFERENCES answer(id);
) Engine=innodb;