[Phpmyadmin-devel] about choosing table layout for pmadb
Hi, Zeeshan is proposing a new table to store user preferences: http://zixan.info/2009/06/04/gsoc-week-two-progress/#comments Michal answers in the blog that this does not scale. When looking at Zeeshan's proposal, I had the same idea for a moment. However, after some researches, I am tempted to reject Michal's proposal. Michal's proposed solution (a table with key values) is also called the EAV (entity-attribute-value) model. http://en.wikipedia.org/wiki/Entity-attribute-value_model#Downsides This is advocated as a design flaw by some experts, see the comments here: http://www.devshed.com/showblog/22280/Database-Design-Using-KeyValue-Tables/ http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ Marc
Hi, Sorry I answered on the blog too - Michal's proposal has been clarified already. In your NavigationBarIconic example you could consider bitwise stuff 0 - nothing 1 - option 1 2 - option 2 3 - option 1 and 2 4 - option 3 5 - option 1 and 3 etc - Mike On 5/06/2009 11:25, Marc Delisle wrote:
Hi,
Zeeshan is proposing a new table to store user preferences: http://zixan.info/2009/06/04/gsoc-week-two-progress/#comments
Michal answers in the blog that this does not scale. When looking at Zeeshan's proposal, I had the same idea for a moment. However, after some researches, I am tempted to reject Michal's proposal.
Michal's proposed solution (a table with key values) is also called the EAV (entity-attribute-value) model. http://en.wikipedia.org/wiki/Entity-attribute-value_model#Downsides
This is advocated as a design flaw by some experts, see the comments here: http://www.devshed.com/showblog/22280/Database-Design-Using-KeyValue-Tables/
http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/
Marc
------------------------------------------------------------------------------ OpenSolaris 2009.06 is a cutting edge operating system for enterprises looking to deploy the next generation of Solaris that includes the latest innovations from Sun and the OpenSource community. Download a copy and enjoy capabilities such as Networking, Storage and Virtualization. Go to: http://p.sf.net/sfu/opensolaris-get _______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Hi Dne Thu, 04 Jun 2009 19:25:40 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Zeeshan is proposing a new table to store user preferences: http://zixan.info/2009/06/04/gsoc-week-two-progress/#comments
Michal answers in the blog that this does not scale. When looking at Zeeshan's proposal, I had the same idea for a moment. However, after some researches, I am tempted to reject Michal's proposal.
I know it's not perfect solution. But when I look at our configuration file, we have far too many options, to represent them as columns in table. Furthermore adding new option would mean to force users to upgrade the table. -- Michal Čihař | http://cihar.com | http://phpmyadmin.cz
Personally, I do not mind implementing any of the structures. In my opinion, upgrading should not really be a problem. I will be writing a little tool to auto upgrade the table. And I don't think we will be adding new settings to be tracked every so often, so it will just be a regular upgrade. Sure, there are several settings in the configuration file that may complicate the table structure. Thanks! -------------------------------------------------- Best regards, Zeeshan Mughal Email: zeeshanmughal@ieee.org Web: http://www.zixan.info On Fri, Jun 5, 2009 at 1:35 AM, Michal Čihař <michal@cihar.com> wrote:
Hi
Dne Thu, 04 Jun 2009 19:25:40 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Zeeshan is proposing a new table to store user preferences: http://zixan.info/2009/06/04/gsoc-week-two-progress/#comments
Michal answers in the blog that this does not scale. When looking at Zeeshan's proposal, I had the same idea for a moment. However, after some researches, I am tempted to reject Michal's proposal.
I know it's not perfect solution. But when I look at our configuration file, we have far too many options, to represent them as columns in table. Furthermore adding new option would mean to force users to upgrade the table.
-- Michal Čihař | http://cihar.com | http://phpmyadmin.cz
------------------------------------------------------------------------------ OpenSolaris 2009.06 is a cutting edge operating system for enterprises looking to deploy the next generation of Solaris that includes the latest innovations from Sun and the OpenSource community. Download a copy and enjoy capabilities such as Networking, Storage and Virtualization. Go to: http://p.sf.net/sfu/opensolaris-get _______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Hi Dne Fri, 5 Jun 2009 01:54:28 -0400 Zeeshan Mughal <zeeshan.jp@gmail.com> napsal(a):
Personally, I do not mind implementing any of the structures.
In my opinion, upgrading should not really be a problem. I will be writing a little tool to auto upgrade the table. And I don't think we will be adding new settings to be tracked every so often, so it will just be a regular upgrade.
New settings usually come in each "big" release (second number changes), what I think is quite often for forcing table upgrades. So far we stick with same table structures for quite a long time... -- Michal Čihař | http://cihar.com | http://blog.cihar.com
Michal Čihař a écrit :
Hi
Dne Fri, 5 Jun 2009 01:54:28 -0400 Zeeshan Mughal <zeeshan.jp@gmail.com> napsal(a):
Personally, I do not mind implementing any of the structures.
In my opinion, upgrading should not really be a problem. I will be writing a little tool to auto upgrade the table. And I don't think we will be adding new settings to be tracked every so often, so it will just be a regular upgrade.
New settings usually come in each "big" release (second number changes), what I think is quite often for forcing table upgrades. So far we stick with same table structures for quite a long time...
Sorry Michal, I don't get your point. Are you saying that auto-upgrade would not be appropriate? Marc
Hi Dne Mon, 08 Jun 2009 11:10:06 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Sorry Michal, I don't get your point. Are you saying that auto-upgrade would not be appropriate?
With our current suggested way of creating pma user there is no way to do autoupgrade easily - pma user does not have enough privileges, so you need to ask user for more privileged account to do the upgrade. -- Michal Čihař | http://cihar.com | http://blog.cihar.com
Michal Čihař a écrit :
Hi
Dne Mon, 08 Jun 2009 11:10:06 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Sorry Michal, I don't get your point. Are you saying that auto-upgrade would not be appropriate?
With our current suggested way of creating pma user there is no way to do autoupgrade easily - pma user does not have enough privileges, so you need to ask user for more privileged account to do the upgrade.
I guess that the control user will need more privileges. Also developers will have to take special care of the "downgrading" situation, where we play with older versions of phpMyAdmin on the "upgraded" pmadb. Marc
Michal Čihař a écrit :
Hi
Dne Thu, 04 Jun 2009 19:25:40 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Zeeshan is proposing a new table to store user preferences: http://zixan.info/2009/06/04/gsoc-week-two-progress/#comments
Michal answers in the blog that this does not scale. When looking at Zeeshan's proposal, I had the same idea for a moment. However, after some researches, I am tempted to reject Michal's proposal.
I know it's not perfect solution. But when I look at our configuration file, we have far too many options, to represent them as columns in table. Furthermore adding new option would mean to force users to upgrade the table.
Yes there are downsides for each alternative. Another downside for the EAV model is that at user login (session initialization) we need to read all the rows holding the user prefs for this user, instead of just one read if each option has its own column. As mentionned in one of the links I gave, the EAV model also makes it difficult to code the possible choices for an option, for example into an ENUM. About upgrading, I expect Zeeshan to implement a feature to auto-create and auto-upgrade the table (which could be extended to all of our pmadb tables). Marc
Hi Dne Fri, 05 Jun 2009 05:32:02 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Yes there are downsides for each alternative. Another downside for the EAV model is that at user login (session initialization) we need to read all the rows holding the user prefs for this user, instead of just one read if each option has its own column.
With index on login name, selecting several rows should be quite cheap.
As mentionned in one of the links I gave, the EAV model also makes it difficult to code the possible choices for an option, for example into an ENUM.
Okay, I probably can live with either of solutions. Both have downsides and advantages.
About upgrading, I expect Zeeshan to implement a feature to auto-create and auto-upgrade the table (which could be extended to all of our pmadb tables).
It should be coded with this in mind. -- Michal Čihař | http://cihar.com | http://blog.cihar.com
Michal Čihař a écrit :
Hi
Dne Fri, 05 Jun 2009 05:32:02 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Yes there are downsides for each alternative. Another downside for the EAV model is that at user login (session initialization) we need to read all the rows holding the user prefs for this user, instead of just one read if each option has its own column.
With index on login name, selecting several rows should be quite cheap.
As mentionned in one of the links I gave, the EAV model also makes it difficult to code the possible choices for an option, for example into an ENUM.
Okay, I probably can live with either of solutions. Both have downsides and advantages.
I guess it's up to Zeeshan to decide now.
About upgrading, I expect Zeeshan to implement a feature to auto-create and auto-upgrade the table (which could be extended to all of our pmadb tables).
It should be coded with this in mind.
:-) -------------------------------------------------- Best regards, Zeeshan Mughal Email: zeeshanmughal@ieee.org Web: http://www.zixan.info On Mon, Jun 8, 2009 at 11:18 AM, Marc Delisle <marc@infomarc.info> wrote:
Michal Čihař a écrit :
Hi
Dne Fri, 05 Jun 2009 05:32:02 -0400 Marc Delisle <marc@infomarc.info> napsal(a):
Yes there are downsides for each alternative. Another downside for the EAV model is that at user login (session initialization) we need to read all the rows holding the user prefs for this user, instead of just one read if each option has its own column.
With index on login name, selecting several rows should be quite cheap.
As mentionned in one of the links I gave, the EAV model also makes it difficult to code the possible choices for an option, for example into an ENUM.
Okay, I probably can live with either of solutions. Both have downsides and advantages.
I guess it's up to Zeeshan to decide now.
About upgrading, I expect Zeeshan to implement a feature to auto-create and auto-upgrade the table (which could be extended to all of our pmadb tables).
It should be coded with this in mind.
------------------------------------------------------------------------------ Crystal Reports - New Free Runtime and 30 Day Trial Check out the new simplified licensing option that enables unlimited royalty-free distribution of the report engine for externally facing server and web deployment. http://p.sf.net/sfu/businessobjects _______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
participants (4)
-
Marc Delisle -
Michal Čihař -
Mike Cochrane -
Zeeshan Mughal