database table versus pages

[wilsea]

Still a newbie to both processwire and php.......just checking that my thinking is long the right track

I want to set up a heirachical site with the following structure

Categories (6) > Subcategories (3 per parent) > Sub-subcategories(2-10 per parent) >Items (1-10 per parent).

 

References to the various levels of the heirachy will need to be available to other pages on the site.

I get the idea of doing it in pages with relational page fields, nicely explained in Tutorial: Approaches to categorising site content . It looks as though it is also possible to add  tables directly to the database (or even another database).

It's delightfully easy to use a template to display the data on each page any way you want. It seems there are two ways to access the data.

1. With the data as pages, I could use a selector to query the database.e.g     
   $items = $pages->find("template=mytemplate"); //or any other selector
2.  With custom database tables, I could query the database directly e.g. 
   $result = $this->db->query("SELECT id, name, title, url FROM yourtablename WHERE id=$id");

Is there anything I should know about the pro's and con's of either method before I build the site. The data already exists, but I'll be scraping a website to get it, so it will be added to my site under program control. I'm not sure whether my program should create processwire pages, or just create some database tables. Actually now that I think about it, both are possible,  table first, then pages created from a table.

I'd really appreciate any comments before I unknowingly head off down the wrong track. 

[kongondo]

Hi Wilsea,

It really depends on how you want to use and present your data at the end of the day + how much data we are talking... A search of the forums will tell you that most people would choose pages over custom tables. Why? For one, you get access to the powerful PW API. Of course, behind the scenes, it is all db tables, but PW takes care of that. There is (almost) a selector for any query you would want to make....If this is not the case, you can also query PW tables using normal MySQL queries. 

If you want to present your data at unique URLs, then pages is the way to go.... 

How many tables are we talking? You have probably noticed that PW is insanely fast. Ryan has done a tremendous job in optimizing selectors. With power comes responsibility; so, limit=xx is a good friend when using "find"....

Relational db/References...pages is the way to go...Page Reference Fields in PW make this very easy!!

If you went the pages route, you can also harness the extended power of templates, e.g. using templates to control page access...

For some people, having thousands of pages in the pages tree is not desirable. However, depending on how you structure your site, this may not be a problem. If it is a problem, then you might want to think of custom tables....although, there are ways around displaying your pages in a grid (as in a table) in the backend rather than the tree..Btw, I reckon you are talking about a local db table? (i.e. not external to your domain..). 

Easy-on-the-eyes...PW API is beautiful and easy to read....even though MySQL-like code is easy enough to read as well...

Bottom line is this: If you really don't have to, do not reinvent the wheel. PW already gives you an easy, consistent and powerful way to query your data....using the API..

It is late and I am not thinking straight. I could have missed something. Others will chip in. Also use Google to search the forums (e.g. custom tables...). You might also find it useful to read the various threads detailing how some folks here imported data from other platforms/websites into PW....There are also bulk editing modules such as CSV Import module...

Hope my comments give you a little idea how you might proceed...

Edited: for clarity + additions...

Edited January 13, 2014 by kongondo

[adrian]

Depending on your data structure, you might also consider this approach:

http://processwire.com/talk/topic/5040-events-fieldtype-inputfield-how-to-make-a-table-fieldtypeinputfield/

This example module shows you how to build a custom PW fieldtype that allows you to have the power of PW API while still storing the data in a more traditional way - multiple fields per DB row.

Again, the best option depends on lots of different things, but kongondo has outlined these very well already.

[SteveB]

Kongodo summed it up well.

I would suggest gathering your data from the outside world into conventional database table(s) outside of PW. Set them up so they are easy for you to fill and check over. Then write a module based on Ryan Cramer's module for importing from CSV files. Make it read from your table(s) instead of a file. You can put all your code for massaging the content right in the module. With liberal use of the UI modules have for settings you can use the same tool to make various kinds of pages from input data.

Working with an external database can be pretty simple:  
   
    protected function externalDbConnect(){  
        $this->extDb = new mysqli('localhost', 'xxxx', 'yyyy', 'zzzz');
    }
    protected function queryCount($qry){
        $result = $this->extDb->query($qry);
        return $result->num_rows;
    }        
    protected function queryForFields($table){
        $a = array();
        $result = $this->extDb->query('SHOW COLUMNS FROM '.$table);
        while($row = $result->fetch_array(MYSQLI_ASSOC)) $a[] = $row['Field'];
        return $a;
    }        
 

[wilsea]

Thankyou both for the blazingly fast replies. A big help in clarifying my thinking.

I hadn't thought about URL's, obviously a big point in favour of pages, which got me thinking about url's verus ajax, and bookmarking/back button issues.

Those event field types look really interesting.  I'm really intrigued by how much can be done with a small amount of code in a module! Heady stuff if I can only get my head around it all. I see a table with the data is added to the database, with multiple columns instead of the usual two column field tables.  I guess once the table is created I could add records to it using regular php.  I'm also guessing that I could customise it by changing the fields in the __construct()  array of Event.php,  (Sorry if I'm stating the obvious - I'm still wobbling around on trainer wheels. Just checking to see I'm on the right path).

 

If I was using a regular mySQL database there would be about 5 or 6  tables, most of them very small. The biggest one will have about 2000 records. The data is static - no CRUD at all once it's in place, but other pages on the site will reference to it, and the data will need to be easily browsed and displayed by various search configurations.

Still mulling over the options over the best way to go.

Thanks very much for the suggestions. They've given me a lot to think about.

[adrian]

Yep, you can definitely customize the fields from the events example. That module, although useful in its own right, was meant as an example to be modified to your needs.

Let us know if you have more questions once you dive in!

[wilsea]

Thanks SteveB, Adrian. Looks like it might be time to tackle the modules tutorial.

[Pete]

I'd probably still do it as pages to be honest, just because it's easier and we're not talking about a lot if data by the sound if it.

Any chance you can post the database structure? That would give us a better idea as to which way might be the more appropriate route

[wilsea]

Thanks Pete, delighted to post the database structure. Basically it's a simple 5 level heirachy, one to many down through each level. 

Level 1 - Subject - 6 records (name only)

Level 2 - Sub-subject - 18 records (name only)

Level 3 - strand -  40 records (name only)(3rd letter of topic_code)

Level 4 - topic - 1000 records (4 fields + id) (4th and 5th letters of topic_code)

Level 5 - details - 3000 records (2 fields + id)

Subject and sub_subject probably don't need to be in the database, as they are pretty small and consist of only a name field. Fortunately the topics table has a topic_code field which can be parsed to get information about the subject and sub_subject. The topic_code is a unique field about 98% of the time.    topic_code plus topic_level is always unique.

Id fields at the top level of each table are auto-increment numbers.

Likely searches and groupings will be on subject, sub_subject, strand and topic_level (there are only 10 topic_levels). Page fields for each of these.will be handy for the rest of the site.  It's highly unlikely that the details table will ever need to be searched directly, but the topics table will often have records retrieved by the topic_code.

 I rather like the idea of a custom table field like the FiletypeEvents for the topics table, but I'm not sure how to go about setting up the relationship between the topics table and the details table. I haven't figured out what is doing the actual save in the Fieldtype Events. table. It seems to be a submit post to itself, but I can't find any actual save type code any where, and I'm not sure where to look.

Thanks for following this post. Great forum!

[kongondo]

I'd go with pages here. That's no a lot of records (thinking of page tree). Someone in the forums has built a site with 100k pages! 

[Soma]

I just created/imported 45k pages for users infos in about 1min.

[wilsea]

Thanks so much for all the suggestions people. I ended up opting for pages built from four tables, and that seems to everything I want and more.

For the benefit of any future readers.....

While it's probably self-evident, a separate template for each table made the whole process easier to manage.Tables imported like a dream with the import Pages from CSV module. What was even dreamier was correcting a few mistakes or three (having too much fun with  PW to catch up on some sleep) with the ability to do bulk trash/change templates operations with the Batcher module. I found out the comma delimiter is a bad idea if your field already has commas in it (duh). Delete mangled pages, reimport as tab delimited, problem solved.

Linking the tables/pages up inside PW was a joy instead of a nightmare. There's probably better/other ways, but I just coded the templates to provide the functionality (mostly navigation and display) that I needed. I'm wrapt and can't wait to push the envelope further.  

Who'd of thought  Christmas would come round again so quickly.