One table per field: rationale and impact?

[Peter Falkenberg Brown]

Hi Folks,

I started actual work on my PW installation yesterday (I've been reading docs and forum posts prior to that).

I added what I would call the equivalent of a "writers table", by creating some "writers_*" fields, and then a writer_id_select field, which I pulled into a regular template, which then stored the writer_id, which allowed me to use the nifty PW routines to grab the byline etc.

I kept SQLYog open as I did all this, so that I could examine the new data.

I noted once again that each field, like "writers_byline", created its own data table.

I think I understand the flexibility issue for that method, but I'm wondering about the impact of that design, in Process Wire. For example:

- I read somewhere, a long while ago, that having thousands of tables in a database can degrade MySQL performance. Yet, with a very large web app, or CMS system, there could indeed be a huge amount of tables, since each field has its own table.

- Normally, with one data table, with many fields, one opens a row, and pulls in all the fields at once. With the PW method, each table has to be opened, for each field. I wonder what kind of slowdown or system drag or server load happens with that, because it's not just one query, for a row of fields, it's many queries, in order to get all those fields from their own tables.

- When one exports a data table, like a "contacts" table, you get one file for the table. In fact, I wrote a Perl script called "MySQL Backup" that does just that; one file per table. Now, that same script would create one file per field, and I can image that trying to access that data from an application outside of PW, i.e. via a restore to that other app, would be quite arduous, since you'd have to reassemble all the separate fields into one table, unless you were willing to create huge relational queries, e.g. "where id=id and id=id.... ".

So, these are some thoughts and questions about the rationale that would create this method, i.e. the pros of one field per table have to outweigh the cons of the traditional method.

And of course, could there be a point where the system breaks down because of too many tables in MySQL; too many queries, etc?

Anyone have any thoughts?

Thanks!

Peter

[WillyC]

http://processwire.com/talk/topic/2387-the-structure-of-fields-and-templates/?p=22762

[Peter Falkenberg Brown]

Dear WillyC,

Thanks, that's a great forum post by Ryan that you linked to. I don't like asking questions that have already been answered, but somehow I didn't find that post. It would be a great item to pin to the FAQ.

I think it answered my main questions, but the issue of exporting and importing data sets for some other purpose is still problematic. And... if fields are shared, as Ryan suggested, it gets very complicated indeed.

A PW export utility would be very handy, some day.

Peter

[teppo]

Hello there Peter,

This may not be of much help, but still wanted to point out that exporting data from ProcessWire for specific needs, ie. where you can easily define what data / which parts of your site / which templates you wish to export is relatively easy to achieve, just not with direct SQL queries:

Bootstrapping PW and writing simple command-line script that exports data is one method I've used in the past. Another method, which is especially helpful if you only need to export specific templates, is creating a template file that instead of HTML serves JSON, XML, CSV or whatever format you prefer, accompanied with proper headers of course.

If you need to export multiple pages, it should again be easy to write a template file that finds pages you need and renders each of them one after another with render() method of Page object. This is actually something I've just recently used for Excel export on a relatively large client site, accompanied with various user-defined search criteria. Works like a charm.

You could also combine these two by writing a command-line script that does exactly what I've described above for exporting multiple pages simultaneously. As a neat little trick you could also switch template file used for rendering by altering the value of $page->template->filename on the fly per page in order to have "export template file" while still avoiding any unnecessary changes or complex logic at the site itself.

This is still pretty far from generic export method, but has so far fulfilled all my export needs perfectly fine. PW doesn't force any limits on what you can do with your template files, which is one of the reasons I find it such a wonderful platform for pretty much anything web-related.. and even some non-web-related things

[Peter Falkenberg Brown]

Dear Teppo,

Thank you for these tips; they all sound excellent.

I'll be digging into PW more deeply during the next few weeks, so I'm sure I'll be enlightened.

I built a web framework in Perl that I still use all the time, but it doesn't scale well, in terms of traffic (because I'm not using mod_perl),

so I'm very happy to find ProcessWire.

Thanks again,

Peter

[Pete]

The other thing to note since ryan wrote that post Peter is that you can change the label for a field on a per-template basis now.

So for example if you have a body field but want to call it something else, you can add it to a new template, click on the field name and change the label for that specific template. That way if it makes more sense to call it something else but makes it easier to refer to it as "body" in searches you can still get the benefits of re-using the field but giving an appropriate name to any editors.

[Peter Falkenberg Brown]

Dear Pete,

That's a good point! Thanks.

Peter