CODEWITHSUNDEEP
mysql
iceangel89
iceangel89

Reputation: 6273

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

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

Upvotes: 1

Views: 2477

Answers (2)

Joel Small
Joel Small

Reputation: 197

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.

Upvotes: 0

soulmerge
soulmerge

Reputation: 75704

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;

Upvotes: 3

Related Questions