Jump to content
fruid

unusual Database structure

Recommended Posts

ProcessWire's database structure is unusual when first looking at it... Alien in fact.  I had a similar reaction in the beginning. But then as you use the system, especially if coming from other content management systems, you'll see that it's very well thought out, flexible and performant. If you compare it to WordPress which has a more traditional structure combined with the post meta table, you'll see where WordPress' approach breaks down.  Menus in WP get shoved into the post and post meta table in a really hacky way... Not good. Woocommerce from what I understand now uses their own table structures as opposed to the default WP ones.  Slow Woocommerce sites were a big problem.

Regardless of the system, editing data directly in MySQL is a bad idea. If I had a potential client that insisted on this, it would be a huge red flag. If they want to get hands on and edit data programmatically, I'd recommend learning the basics of manipulating pages via the API which may satisfy their need.

  • Like 2

Share this post


Link to post
Share on other sites

Your client is wrong in stating this is not relational. Tables and relationships between them is what processwire does. Just differently than your client seems to be expecting it.

On the other hand I feel you did miss out on proper up front communication about the requirements of your client. It's great if you want them to move on from joomla, but moving to a different system, which doesn't work for your client is worse than an old problematic one. I'd try to get back to the drawing board and evaluate more closely what the needs of your client actually are, do some demos with processwire and see if it actually can fit or not.

20 minutes ago, Jonathan Lahijani said:

WordPress which has a more traditional structure

Maybe for as long as it's "stock" wordpress, but even then things like the guttenberg editor are unstructured data in wp. I'd not say it's a good example of an relational database structure. Even more so considering how you interact with those records within php.

  • Like 2

Share this post


Link to post
Share on other sites
1 hour ago, fruid said:

the client insists that PW's database structure is not relational because some fields are redundant. Now I'm in doubt, did I screw up somewhere? As mentioned above, I thought some problems might arise when re-using the same fields on different templates, maybe that's what this is about?

As @LostKobrakai and @Jonathan Lahijani pointed out above, ProcessWire's database indeed is relational. I must also admit that I have no idea what some fields being redundant means — field tables with no values? Fields that don't have values for a specific page? Something else?

Either way this is not something you messed up. Unless you've manually modified the values in the database (or the structure, tables, etc.) the database works exactly as it was designed to work, whether or not your client comprehends how it works or why it was designed that way. Also: reusing fields is the best practice, while creating a new set of fields for each template is something I'd advice against (it's bad for performance, will make selectors less useful, etc.)

It's true that in what might be the most typical form — particularly for custom-built, one-off applications — each table represents a specific content type (pages, blog_posts, users, etc.) ProcessWire's database structure was designed for custom content types, so this "typical form" doesn't exactly apply here. Instead (at the most basic level) we've got a "master" table for pages, and then a new table for each field, connected to the pages table by the pages_id column.

Anyway, I believe this has been explained a number of times already, so let's leave it at that. The long story short is that this is how ProcessWire was built to work, and this part cannot be changed.

1 hour ago, fruid said:

Also, now I'm mixing ProFields Table and ListerPro, not sure if that is wise, ProFields Table, to my understanding, simulates (for lack of a better word) a non-relational database but it really just is a field with subfields?

Behind the scenes ProFields Table creates a single database table for each field. So far it's exactly like any other field, but the big difference is that it actually allows one to modify the structure of that table via GUI, while for most other field types the table structure is "set in stone" (defined as codified rules within a Fieldtype module).

The main benefit of a ProFields Table field is that it can very efficiently hold multiple (interconnected/related) value items — or table rows. A Repeater or PageTable field does the same, but behind the scenes each Repeater or PageTable item is a Page, and each property (subfield) is a field value. Thus the Repeater (or PageTable) approach comes with a different set of constrictions and benefits.

(You could reap pretty much the same benefits by developing custom per-field Fieldtypes, but that takes a lot more effort than just creating and configuring a new ProFields Table field 🙂)

I sincerely hope that you can convince your client that managing content handled by ProcessWire directly via the database is not the right approach. I definitely get that this is a frustrating situation, both for you and your client.

  • Like 1

Share this post


Link to post
Share on other sites

I installed ImportPagesCSV (thanks to Ryan) which seems to work like a charm, at least for pages – need to see for ProFields Table. The client has no problem with using that instead of SQL. Am I right to assume that this module is making proper use of the API?

Upon upload, I hope that we'll be able to use ListerPro for bulk edits, even for ProFields Table and everything will be alright.

Share this post


Link to post
Share on other sites
1 hour ago, fruid said:

Am I right to assume that this module is making proper use of the API?

Which one? ProFields Table? If yes, definitely; Ryan's the author 🙂 (although I am not sure what you mean exactly by proper use of the API).

Share this post


Link to post
Share on other sites
10 hours ago, fruid said:

the client insists that PW's database structure is not relational because some fields are redundant

Hey, no offense but your client sounds like a real punish. Here is a simple definition of a relational database pulled from the web:

"A relational database organizes data into tables which can be linked—or related—based on data common to each".

In PW each field gets its own table in the db, and each of these tables starts with a  "pages_id" column (the primary key) that "relates" the data in a row to a specific page. This is by definition an example of following the relational model in database design.

If you are seeing empty tables in the db, that is because you have fields that have no data associated with them. Perhaps try cleaning up some of your unused / redundant fields.

It seems that some of @bernhard 's amazing Rock modules might help here. Have a look at the below, and check the post on the original RockGrid module (the precursor to RockTabulator) for examples of how it can be used:

 

  • Like 2

Share this post


Link to post
Share on other sites

Thx for the kudos @Mikie but I think my modules will not make @fruid's clients happy. My modules are solving the problem of finding and/or displaying data, but are not doing anything in regards of bulk-editing data quickly 🙂 

  • Like 1

Share this post


Link to post
Share on other sites

Ah thanks for the heads up @bernhard I never got to play with them all I thought at least one of them let you edit. Sorry for the red herring.

Share this post


Link to post
Share on other sites

Yeah you can edit entries of course, but with a link that opens a panel or a new tab. Nothing that is great for bulk editing of many entries at once 🙂 

Share this post


Link to post
Share on other sites

so as mentioned above, I'm trying to use a combination of ProFields Table and ListerPro. I exported the table I created with the PageActionExportCSV within ListerPro to see how the data is structured so that I can then upload another list using the module ImportPagesCSV.

But in the exported CSV file all the values of any column are in the same table cell of the same row instead of each in a new row, no matter what I do with Multi-value separators, CSV delimiters or CSV enclosure. Is that intended? If so, please explain why… 

Share this post


Link to post
Share on other sites
17 hours ago, fruid said:

I exported the table I created with the PageActionExportCSV within ListerPro to see how the data is structured so that I can then upload another list using the module ImportPagesCSV.

moderator note

This seems like a question specific to ListerPro. If you have a current subscription to that module, please ask this question in the ListerPro support forums. Otherwise, please start a new thread in General Support. We strive for one issue per thread please. Thanks.

Share this post


Link to post
Share on other sites

just thought-showering here; would it be possible to replace the admin's entire cascade-style-dropdown-tree-layout (whatever you call it) to a table-layout instead or default to the finder-module? Might there be an admin-theme for that?

Share this post


Link to post
Share on other sites
14 hours ago, fruid said:

just thought-showering here; would it be possible to replace the admin's entire cascade-style-dropdown-tree-layout (whatever you call it) to a table-layout instead or default to the finder-module? Might there be an admin-theme for that?

Not sure about a table layout (of course, you could build it yourself!). But you can change the default process you see after logging in easily. In the page tree, find the Admin page and go to edit. There you'll find a Process option. Switch this from the default (ProcessPageList) to ProcessPageLister to show the page lister by default. If you're using ProLister, the Process might have a different name, not sure right now. After saving, logging in or clicking on the logo in the admin bar should take you straight to the page lister.

  • Like 2

Share this post


Link to post
Share on other sites
On 8/26/2020 at 4:53 AM, fruid said:

just thought-showering here; would it be possible to replace the admin's entire cascade-style-dropdown-tree-layout (whatever you call it) to a table-layout instead or default to the finder-module? Might there be an admin-theme for that?

It's possible to hide the page tree, and redirect users on login to some other page.

Lister Pro allows you to create your own pages that show data in custom table layout that are editable, and it's not too hard to create your own admin dashboard module with links to several listers.

Here's a thread with some discussion of both: 

 

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...