maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

Hi

I agree that EAV is a pain in the ass. Having several very similar tables that represent flavors of the same "parent entity" is a pain too. Dynamic Columns are logically simple.

I think that the problem is that people don't really know them. And if one tries to explore them, he finds a syntax that is not really easy. Also they are slow, unless you index them, but indexing a dynamic column is not easy - also, requires more space than a normal index, because you are supposed to create a persistent column first.

I don't have any suggestions, except that importing data from JSON would be easier and probably useful (something like a JSON_TO_DYNCOL function).

Regards
Federico


--------------------------------------------
Gio 29/1/15, Adam Scott <adam.c.scott@xxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] The future of Dynamic Columns
 A: "Oleksandr Byelkin" <sanja@xxxxxxxxxxxxxxxx>
 Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 Data: Giovedì 29 gennaio 2015, 02:20
 
 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 http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model)
 
 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.
 
 Adam
 
 
 On Wed, Jan 28, 2015 at
 3:41 PM, Oleksandr Byelkin <sanja@xxxxxxxxxxxxxxxx>
 wrote:
 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
 
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 
 Unsubscribe : https://launchpad.net/~maria-discuss
 
 More help   : https://help.launchpad.net/ListHelp
 
 
 
 -----Segue allegato-----
 
 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp