Maintaining a column for each datatype

I have a function that is suppose to store variables based on their datatype, and this is table I have come up with to store those variables into.

Is it smarter to have everything broken down like this or would be wiser to only have columns for the largest for the largest datatype of a group (such as string datatypes only having a MEDIUMTEXT)? I am expecting this table to become quite large and I would like to keep the tables storage space demands as small as possible.

CREATE TABLE IF NOT EXISTS form_part_detail(
form_part_detail_id  INT NOT NULL    AUTO_INCREMENT  PRIMARY KEY,
form_part_detail_type_id   INT NOT NULL,
label   VARCHAR(255) NOT NULL,
`string`    VARCHAR(255)  NULL,
`text`   TEXT   NULL,
`mediumtext`    MEDIUMTEXT NULL,
`integer`   INT NULL,
`boolean`   TINYINT(1)  NULL,
`float` FLOAT NULL,
`double`    DOUBLE NULL,
FOREIGN KEY (form_part) REFERENCES `form_part` (form_part_id)
FOREIGN KEY (form_part_detail_type_id) REFERENCES `form_part_detail_type` (form_part_detail_type_id)
);

I would probably tackle it like this:

-
The database would have a few columns:

• id

• label

• data (of some type, say, varchar for now)

-
The Wrapper class would be used to store/retrieve data from the database:

• Responsible for conversion (serialization) from whatever type is necessary to the backend data store

The Wrapper class could have some sanity checks so that you couldn't ask for a int if it really was a string, etc etc.

This probably doesn't satisfy your "keeping things as small as possible" requirement, but disk is cheep.

You could create an object and basically serialize that and store the serialized version in the database. You could then deserialize that when you needed to.

StackExchange Code Review Q#2477, answer score: 2