How do you handle multiple value types in MySQL when inheritance isn't supported?

Question

I need to create a table of attributes where each record is essentially just a name-value pair. The problem is that the value can be a string, integer or decimal and I'm using MySQL which doesn't support table inheritance. So, the question is - should I create a separate table for each value type or should I just create str_value, int_value and dec_value columns with an additional value_type column that tells you which type to use? There won't be many records in this table (less than 100), so performance shouldn't be much of an issue, but I just don't want to make a design decision that's going to make the SQL more complex than it has to be.

Answer 1

If you create different columns for each type, you're going to have to be smart about it in your code anyway...

Why not just store everything as a string and convert to the desired type in your code?

Answer 2

When querying the database, you will always receive strings, no matter what the column type is - so there is no reason to make a design decision here - just store everything as a string.

By the way: Having an additional value_type column is redundant - the entry has the type of the only column that has a not-null value.

Answer 3

Having different tables, or even multiple columns where only one of the 3 are populated, is going to be a nightmare. Store it all as varchar along with a type column.