← Back to team overview

maria-discuss team mailing list archive

Re: The future of Dynamic Columns


Hi Adam,

Yes, application developers encounter that problem a lot. Please read the
Motivation section here:

If that describes the general problem adequately to you, maybe give it a


From:  Adam Scott <adam.c.scott@xxxxxxxxx>
Date:  Wednesday, January 28, 2015 at 8:20 PM
To:  Oleksandr Byelkin <sanja@xxxxxxxxxxxxxxxx>
Cc:  Tom Worster <fsb@xxxxxxxxxx>, maria-discuss email list
Subject:  Re: [Maria-discuss] The future of Dynamic Columns

Just wanted to add, any application that wants to store forms data without
having to create a new table every time you create a new form could benefit
from dynamic columns.

In the database design world, any design that is of the EAV
(Entity-Attribute-Value) style would benefit from dynamic columns.  Having
to extract and analyze EAV tables, for the developer, is tricky and
burdensome.   This model seems to show up in the literature first in Medical
settings and is used in  basic science research as well. (see

For those that need it, dynamic columns is powerful and makes life much
easier.  Unfortunately, often those that need dynamic columns do not know it
exists.  If they did, they would jump for joy.


On Wed, Jan 28, 2015 at 3:41 PM, Oleksandr Byelkin <sanja@xxxxxxxxxxxxxxxx>
> Hi, Tom!
> On 28.01.15 23:01, Tom Worster wrote:
>> While functions for getting and setting via json might be useful, I think
>> they are peripheral to the core datatype problem here.
>>  The interface to dynamic columns in Maria is SQL. The internal datatypes are
>> SQLish. We can be completely sure of what was set, in SQL terms, by a
>> COLUMN_CREATE(). But if we use a JSON getter then we might get something
>> different because JSON datatypes don't match SQL datatypes. Sometimes it's no
>> problem but not in general -- depends on the datatypes involved and the
>> application's tolerance of differences.
> AFAIK we lack only boolean type and arrays (could be emulated now but you
> can't say if it is array or object with numeric keys) to fit json types.
> column_create/alter do not require type (detect automatically), so type there
> can be mentioned to enforce some encoding you want (store dates as dates but
> not string)
> column_get() require type of output, if you do not care use CHAR. But this is
> required by SQL expression implementation. There is json output which do not
> need type of columns.
>> What's missing is a getter that tells us the value and datatype of a dynamic
>> column, a getter that tells us what was set.
>> What if we had a getter that returns a string like:
>> COLUMN_GET_SQL(`product`, 'price')
>>>> >> "CAST(123.456 AS DECIMAL(11,3))"
>> And Maria promises us that we could use that expression to write the value
>> back and the result will be the exact same thing. A sort of idempotency
>> promise (kinda).
>> This doesn't make the interface any less clunky (on the contrary) but I have
>> resigned myself to a clunky interface. The only alternative is to forget all
>> this and start something different. SQL is an insanely clunky old language
>> and that's why we use some kind of abstraction software to make it nicer. If
>> we're going to use SQL we may as well try to use what's there.
>> Something like this COLUMN_GET_SQL() would at least allow the application the
>> possibility to get back what it put in.
> I am not sure that I understand what you need. Is it ability to know internal
> storage type?
> [skip]
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> <https://launchpad.net/~maria-discuss>
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~maria-discuss
> <https://launchpad.net/~maria-discuss>
> More help   : https://help.launchpad.net/ListHelp
> <https://help.launchpad.net/ListHelp>