1

I'm currently using MYSQL Workbench to design a database for a Joomla component and I've come across a stumbling block in the design.

Problem description:

I want to allow the admin to create fields for the front-end user to manage. The field is to be determined by the admin in terms of the type and length.

My design:

ADMIN_FIELDS (this is the table to store admin defined fields)
id    INT
name  VARCHAR(45)
type  VARCHAR(45)

USER_FIELDS (this is the table to store user information in the fields defined by admin)
id        INT
value     LONGTEXT
admin_field_id FK_ADMIN_FIELDS

Questions:

  1. Since I do not know the length of the value field, I've chosen LONGTEXT as the datatype. This could range from a boolean to a LONGTEXT size, depending on what the admin select as the datatype. Is this a good database design technique given my problem? Is there a better way?

  2. Is there a performance hit if I use LONGTEXT for something as small as a boolean? In the worst case scenario, if the admin decides to go all out crazy with boolean on all fields of his site, and hypothetically speaking he has a billion users who creates these values. Will the site suffer a performance hit then?

  3. If there is no performance hit, then why do we even bother to store data into relevant data type. Why not use LONGTEXT for everything?

Thanks in advance for your answer.

user3804927
  • 749
  • 1
  • 7
  • 15
  • Have a look at http://stackoverflow.com/questions/923778/how-would-you-design-your-database-to-allow-user-defined-schema, especially Bill Karwins answer and read the linked documents of this answer. It's a database anti pattern. – VMai Aug 12 '14 at 11:54
  • You might want to use a CCK and save the hassle. http://extensions.joomla.org/extensions/authoring-a-content/content-construction – Riccardo Zorn Aug 13 '14 at 00:19

1 Answers1

1

Thanks for your comments. I've since done some research and found a solution tailored for my needs. I'm basically designing the component to allow admins to create custom tables. As such, the component will store admin defined table schemas.

I've chosen not to use a Cck or other form extensions because I want the component to handle smart integration with other components. So in essence the component will be interfacing with the database directly while allowing other components to call on my component easily.

user3804927
  • 749
  • 1
  • 7
  • 15