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

take a look at this question: http://stackoverflow.com/questions/200394/one-table-or-many — nickf, Sep 07 '09 at 12:47

score 3 · Accepted Answer · edited Nov 19 '12 at 09:54

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_string (
    id INT,
    value TEXT NOT NULL,
    FOREIGN KEY parent_key (id) REFERENCES answer(id);
) Engine=innodb;

score 0 · Answer 2 · edited Apr 12 '17 at 07:31

I have to agree with soulmerge's answer, though the obvious inherent issues in this is having additonal joins for all of the individual data types whenever you want to fetch a result for a core question.

It makes fetching the data more complicated, but at least you're preventing incorrect data being recorded.

In a similar post, an answerer recommends a completely different approach, having one particular column as varchar and store all the results.

https://softwareengineering.stackexchange.com/questions/168578/having-a-generic-data-type-for-a-database-table-column-is-it-good-practice

I hate not keeping related data in the same record, but at the same point, foregoing the power of data structures seems absolutely pointless.

+1 for storing data in tables for specific data types. At least here, the data must conform to storage rules.

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

2 Answers2