[pmwiki-users] Would the sqlite recipe gain anything in storing pages in separate tables based on group?

shout at joshleepictures.com shout at joshleepictures.com
Thu Aug 23 04:46:03 CDT 2012


hi alex

in this case group is just a qualification for the dabase item. it does not make a lot of sense to have similar items in different tables. it makes the database more complex than it has to be and it makes later changes much more complicated. without knowing the sqlite recipe, I assume that it stores all pages in one table. then it either has its own table for the group relations or uses a field on each page item for the group relations (which would make sense only in this case, because pmwiki assumes that a page cannot be child to multiple groups).

that said I would strongly advise against creating tables for the single purpose to keep the sql queries as simple as possible. tables should be considered as groups for a certain type of object. in that case the table ‘pagestore’ (or whatever it is called in the recipe) contains all page objects. this way whenever queries need to be run on pages, it knows to look in the pagestore table. if you wanted to split this table into groups – e. g. pagestore-site, pagestore-pmwiki, etc. – you would need to create another table, e. g. ‘table-group-index’ to keep track of all those tables and make them known to the static recipe.

when designing database structures always remember to ask yourself what kind of items (objects) you want to manage. then determine the number of tables that actually store content (rather than store relations) by the number of unique item structures you encounter. e. g. in pmwiki you would probably want to at least store pages, groups and cached pagelists each in its own table. storing pages in different tables would not make a lot of sense (since the data structure of pages is always identical).

best regards
josh



On Aug/22, 2012, at 1923 , Alex Eftimiades wrote:

> I know it has been a while since this was brought up, but I have thought about it a lot since then, and I had one more question about this:
> My wiki uses pagelists extensively--in fact every page has a complicated pagelist as a "group footer" that lists related pages and generates maps. Since I always have the pages searched by group, it seems like in this case it might still make sense to create separate tables for each group because for every direct page request, every page in the database will be read. With separate tables for different groups you would only have to pull up the tables in the groups you are searching in.
> 
> This seemed to make sense to me, and I just wanted to have someone who knows databases and/or the sqlite recipe confirm or refute my hypothesis.
> 
> Thanks,
> Alex
> 
> 
> On Aug 6, 2012, at 11:17 PM, Petko Yotov wrote:
> 
>> Alex Eftimiades writes:
>>> 	I just got the sqlite recipe working with all the pages switched over to the database, and after looking at the database structure, I thought it was odd that it did not use separate tables for different groups. I would think this would cut down on the time it takes to access pages, but I could be wrong.
>> 
>> Yes, having all pages in a single table cuts down the time it takes to access a table and a page in the table.
>> 
>>> I do not know very much about databases in general.
>> 
>> Generally (and in this case too) when records (here: wikipages) have the same structure, they are better to be in the same table, not in different tables.
>> 
>>> I was just wondering whether this was worth working on.
>> 
>> Probably not, if you want to optimize the recipe.
>> Probably yes, if you want to learn about managing SQLite databases with PHP.
>> 
>> Petko
>> 
>> 
>> _______________________________________________
>> pmwiki-users mailing list
>> pmwiki-users at pmichaud.com
>> http://www.pmichaud.com/mailman/listinfo/pmwiki-users
> 
> 
> _______________________________________________
> pmwiki-users mailing list
> pmwiki-users at pmichaud.com
> http://www.pmichaud.com/mailman/listinfo/pmwiki-users




More information about the pmwiki-users mailing list