Which is the best way of adding new field in a table?

[PGS]

In my component, I need to give the user the ability to add new fields in a table.

I've made two variations:

1. I add fields in the table (with an sql like 'ALTER TABLE table_name ADD column_name datatype').

2. I have a field in the table (named 'fields') which is json formatted.

So I add a new field: converting json to object, adding new property to object, convert it back to json format and store it in the database.

The first one seems to me that works better, while the second has the disadvantage e.g in filtering.

Seem to be hard to filter a json formatted field,

What is your opinion, which of the two implementations would you follow?

[Allon Moritz]

Neither one of them. Wait till Joomla 3.6 is out and then you have that functionality built in Joomla core. You can test it out here https://github.com/joomla-projects/custom-fields. The code is based on DPFields. To get more information, read that blog post https://joomla.digital-peak.com/blog/198-custom-fields-in-joomla-3-6. Hope to have you on board testing and leaving feedback for com_fields.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send email to joomla-de...@googlegroups.com.
Visit this group at https://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

[PGS]

Ok thanks, but I can't wait till Joomla 3.6 comes out.

I 'll have to choose now one of these ways.

[Allon Moritz]

Then have a look how I did it with DPFields https://github.com/Digital-Peak/DPFields perhaps it helps you to find your way. I'v chosen way 2, but not with json encoded stuff as it is difficult to search for it. Made a relation table. You can even integrate it into your component. Good luck.

On Thu, Apr 7, 2016 at 1:11 PM, PGS <panagiot...@gmail.com> wrote:

Ok thanks, but I can't wait till Joomla 3.6 comes out.

I 'll have to choose now one of these ways.

[PGS]

Thank you very much, I'll try to use your way (that is to copy the way it's implemented in DPFields).

[PGS]

Iaoneo I downloaded and installed the joomla version you suggested in the above link.

From what I see, you use two tables, #__fields and #__fields_values.

So when you create a field, in fact you create a new record in #__fields.

And store it's value in #__fields_values.

So you never, actually, create a field.

Do I understand it correctly?

Is this the approach you suggest?

[Allon Moritz]

Yes, I create the XML part for the form on the fly https://github.com/Digital-Peak/DPFields/blob/master/plg_system_dpfields/dpfields.php#L567.

--

[PGS]

Ok, that's how I implemented it in my own component.

But I see that you (and others) are against adding new fields in the table.

One problem I see with this, is that they might become too many for the table (as a table has a limited 'capacity' of fields).

Is there some other reason against this?

[Allon Moritz]

Upgrades. WHat happens when you want to add a field a customer has already added? Basically you shouldn't change the database structure except on upgrades of your software.

--

[PGS]

I 'm bringing up this old thread of mine in order to see your views about the table structure when we deal with user defined fields.

I used to add fields (columns) in my table ('ALTER TABLE table ADD column').

After your suggestions I changed this to adding records in a table. A record represents a field.

This latter is called 'EAV' (Entity-attribute-value, EAV for short).

Now I get a suggestion that I should abandon EAV and get back to classical table structure.

What do you people think?

(as long as it concerns the question of laoneo 'in case of upgrade, what if a field is already created by the customer', I 'd say we can add a prefix in customer-created fieds. So in upgrade, our new field will not have this prefix, so problem solved)

[Troy Hall]

according to https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model EAV by its very nature is NOT fast compared to standard methods.  Why not just have fields be params like normal so it only needs a single query?
Bear

[PGS]

I don't understand what you men by this: 'have fields be params'.

Could you explain it?

[Troy Hall]

look up using parameters in J!  As far as I know 90% of the "fields" are stored as parameters.
Bear

[PGS]

I know parameters in J!, but it doesn't make sense to store filds as parameters.

Params is a JSON formatted field, how shall I make searches?

[Hannes Papenberg]

What you are suggesting is far as possible away from normalised database
design and thus pretty slow.

Hannes

Am 06.06.2016 um 14:52 schrieb Troy Hall:
> according to
> https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
> EAV by its very nature is NOT fast compared to standard methods. Why
> not just have fields be params like normal so it only needs a single
> query?
> Bear
>
> On 06-Jun-16 05:31, PGS wrote:
>> I 'm bringing up this old thread of mine in order to see your views
>> about the table structure when we deal with user defined fields.
>> I used to add fields (columns) in my table ('ALTER TABLE table ADD
>> column').
>> After your suggestions I changed this to adding records in a table. A
>> record represents a field.
>>
>> This latter is called 'EAV' (Entity-attribute-value, EAV for short).
>>
>> Now I get a suggestion that I should abandon EAV

>> <http://forums.mysql.com/read.php?24,646420,646441#msg-646441> and

>> get back to classical table structure.
>>
>> What do you people think?
>>
>> (as long as it concerns the question of laoneo 'in case of upgrade,
>> what if a field is already created by the customer', I 'd say we can
>> add a prefix in customer-created fieds. So in upgrade, our new field
>> will not have this prefix, so problem solved)
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Joomla! General Development" group.
>> To unsubscribe from this group and stop receiving emails from it,
>> send an email to joomla-dev-gene...@googlegroups.com

>> <mailto:joomla-dev-gene...@googlegroups.com>.

>> To post to this group, send email to
>> joomla-de...@googlegroups.com

>> <mailto:joomla-de...@googlegroups.com>.

>> Visit this group at https://groups.google.com/group/joomla-dev-general.
>> For more options, visit https://groups.google.com/d/optout.
>

> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-gene...@googlegroups.com

> <mailto:joomla-dev-gene...@googlegroups.com>.

> To post to this group, send email to
> joomla-de...@googlegroups.com

> <mailto:joomla-de...@googlegroups.com>.

[Ove Eriksson]

@PGS
What kind of answer are you awaiting?

Yes! A Ferrari is faster than a Fiat 500 on a raceway. If it is faster from A to B in the City of London on a monday morning is not that obvious. ;)

A single table optimised with indexes for fields and combinations of fields is much faster then if you use some "EAV" way. Much faster can be .1 ms instead of .5 ms or 1 s instead of 5 s both 5x faster.

Your decision what to use has to be based on other premises like:
- ease of use, flexibility (who is the user)
- number of items, 10' or 100' (thousands)
- server performence, costs
- and more

Two examples:
The old Joom!Fish translation extension (Joomla 1.5 and 2.x) was using one tabel for all translated fields (in all extensions). I never heard that this gave impossible response times.
The translation extension from redCore (Joomla 3.x) is using 1 table for each table to translate, They argue that this is faster and it probably is.

Another example: If I need a custom user profile for a site, I consider to create a separate table and not use the Joomla! user profile for different reasons. So using a "sub-table" is another possibility for you.

Again: It depends on ....

And it remains your decision, sorry.

[MonkeyT]

This isn't backwards compatible and probably won't be supported officially within Joomla for a very long time for that reason, but MySQL 7 supports a JSON field format.  For example:

SELECT * FROM json_test WHERE JSON_EXTRACT(`json_field`,'$.label')='beta'

I've barely tinkered with it, but I expect it to be slow compared to a traditional search - I'm not planning on using it for any frequent or complex searches.  It is even possible to update the JSON values one component at a time.

If you need to store a value in `params` that you need to search for OCCASIONALLY, AND you have control of the MySQL version you are using, you might want to look this up.  

Tim

[PGS]

Well, I decided to go back to my initial way of adding fields (that is real fields are being added in the table).

I suppose I was expecting from you to assure me that I'm on the right track :)...

Nevertheless, please keep posting your opinions.

[Sergio Manzi]

I think using separate, "ad-hoc", tables linked to the standard ones by they primary index would be a safer choice...

[Hannes Papenberg]

I think your solution is good. Easy profile uses such a method and I like working with such a system

[MonkeyT]

The logic usually flows like this:

If the data is something you never need to search upon, params is the way to go.  It is generally intended to be extendable, but it is not very search friendly.

If the data is something you RARELY need to search upon, params can be used with either LIKE or FULLTEXT searching, but both are limited, complex to set up when using JSON, slow to perform and have drawbacks you need to understand.  If that disturbs you, go with an external table.

If the data is something you commonly search upon, an external table is indexable, fast, efficient and is almost always the proper solution.

Notice, I never mentioned adding fields to an existing table.  If you are working with a table some other software/developer has created and/or maintains, there is no way for them to know that your 'addition' isn't a either garbage or malicious hack of their system.  Every time they update their software, you run a decent chance of having their installer fail for security reasons, report your change as a hack, or even silently remove your change as part of database maintenance.  You run a serious risk of introducing instability down the road by altering someone else's table where they don't intend you to do so.

Tim