Ideal database scheme for eZPublish

Author Message

Thomas Koch

Monday 02 June 2008 12:39:53 am

Hi,

as you may have seen, eZ Systems has recently published an article[1] about datatypes written by me. While I've been writing this, I also thought a lot about the good and the bad parts of the current implementation.
The result of this thinking is a blogpost[2] about a slightly modified database scheme. The major modification is, to create new database tables on the fly for new content classes. This reduces the overhead of fetching unneeded database fields and gives greater flexibility when creating new datatypes.
I'd be happy, if you would like to have a look at the schema and tell me, if you think that it could work.

Please consider this thread as an academic discussion! I do not intend to rewrite eZPublish from scratch. (... for the moment :-)

[1] http://ez.no/developer/articles/creating_datatypes_in_ez_publish_4
[2] http://www.koch.ro/blog/index.php?/archives/75-Changing-Database-Schemas.html

---
Thomas Koch | http://koch.ro
YMC - eZ Publish in Switzerland | http://ymc.ch

Bruce Morrison

Monday 02 June 2008 1:49:08 am

Hi Thomas

I'm not sure what fundamental problem(s) you are trying to solve with your suggestions.

What <i>overhead of fetching unneeded database fields</i> have you seen?

What issues have you had with flexibility? I've created many custom datatypes but have never had a issue with flexibility.

When the existing fields content object attribute fields are not adequate to store the required data thats when I create custom tables, linked using data_int value to hold the key.

I believe there are a several datatypes in the core distribution that act like this. User accounts is one that come to mind.

Cheers
Bruce

My Blog: http://www.stuffandcontent.com/
Follow me on twitter: http://twitter.com/brucemorrison
Consolidated eZ Publish Feed : http://friendfeed.com/rooms/ez-publish

Thomas Koch

Monday 02 June 2008 3:26:28 am

Hi Bruce,

thanks for your reply. I do not think, that I've discovered a fundamental problem with eZ Publish. The fact, that my proposal is only a slight modification to the current implementation is instead a statement, that there is no fundamental problem.

The overhead of unnecessary fetches is, that for each attribute you do a
SELECT data_float, data_int, data_text, ...
even if you need only one field of these.

You already describe the problem with flexibility: If you need more fields, you have to create additional tables and do an additional SELECT.

With my proposal, the additional fields would be inside the contentclass table and you would save one SELECT.

Cheers,

Thomas

---
Thomas Koch | http://koch.ro
YMC - eZ Publish in Switzerland | http://ymc.ch

Bruce Morrison

Monday 02 June 2008 4:42:25 am

Hi Thomas

I'm not sure that your proposal is a slight modification to the current implementation. It not only would require a reworking of the datatypes but also contentclasses & contentobjects.

In terms of the problem you are trying to solve, I just can't get my head around where you are coming from. I get the feeling it's to improve efficiency, less queries or something similar. Can you explain further how flexibility improves with your approach? Maybe give examples?

I'm asking these questions as it then introduces a measure of being able to say if one approach is better than another.

I suspect that the overhead of retrieving some fields that may not be required is minor compared to having to retrieve content objects across a number of different tables for some queries. In the current implementation the "extra" info is only retrieved if required. (this might be solved by moving the meta data into the content_object table)

Regarding extra tables, your proposal has an extra table per content class (per version?), while the existing approach only requires additional tables if required for the specific data.

cheers
Bruce

My Blog: http://www.stuffandcontent.com/
Follow me on twitter: http://twitter.com/brucemorrison
Consolidated eZ Publish Feed : http://friendfeed.com/rooms/ez-publish

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.

eZ debug

Timing: Jan 18 2025 04:14:14
Script start
Timing: Jan 18 2025 04:14:14
Module start 'layout'
Timing: Jan 18 2025 04:14:14
Module start 'content'
Timing: Jan 18 2025 04:14:15
Module end 'content'
Timing: Jan 18 2025 04:14:15
Script end

Main resources:

Total runtime0.4589 sec
Peak memory usage4,096.0000 KB
Database Queries60

Timing points:

CheckpointStart (sec)Duration (sec)Memory at start (KB)Memory used (KB)
Script start 0.00000.0063 589.1484152.6250
Module start 'layout' 0.00630.0027 741.773439.4453
Module start 'content' 0.00890.4484 781.2188556.4688
Module end 'content' 0.45730.0015 1,337.687516.1563
Script end 0.4589  1,353.8438 

Time accumulators:

 Accumulator Duration (sec) Duration (%) Count Average (sec)
Ini load
Load cache0.00340.7309160.0002
Check MTime0.00130.2873160.0001
Mysql Total
Database connection0.00070.156910.0007
Mysqli_queries0.391585.2977600.0065
Looping result0.00050.1140580.0000
Template Total0.430493.820.2152
Template load0.00220.484020.0011
Template processing0.428193.288020.2141
Template load and register function0.00020.036810.0002
states
state_id_array0.00030.062110.0003
state_identifier_array0.00110.231620.0005
Override
Cache load0.00190.4061400.0000
Sytem overhead
Fetch class attribute can translate value0.00050.112320.0003
Fetch class attribute name0.00130.289150.0003
XML
Image XML parsing0.00060.134820.0003
class_abstraction
Instantiating content class attribute0.00000.003360.0000
General
dbfile0.00070.1498160.0000
String conversion0.00000.002240.0000
Note: percentages do not add up to 100% because some accumulators overlap

Templates used to render the page:

UsageRequested templateTemplateTemplate loadedEditOverride
1node/view/full.tplfull/forum_topic.tplextension/sevenx/design/simple/override/templates/full/forum_topic.tplEdit templateOverride template
4content/datatype/view/ezxmltext.tpl<No override>extension/community_design/design/suncana/templates/content/datatype/view/ezxmltext.tplEdit templateOverride template
9content/datatype/view/ezxmltags/paragraph.tpl<No override>extension/ezwebin/design/ezwebin/templates/content/datatype/view/ezxmltags/paragraph.tplEdit templateOverride template
5content/datatype/view/ezxmltags/line.tpl<No override>design/standard/templates/content/datatype/view/ezxmltags/line.tplEdit templateOverride template
2content/datatype/view/ezimage.tpl<No override>extension/sevenx/design/simple/templates/content/datatype/view/ezimage.tplEdit templateOverride template
1print_pagelayout.tpl<No override>extension/community/design/community/templates/print_pagelayout.tplEdit templateOverride template
 Number of times templates used: 22
 Number of unique templates used: 6

Time used to render debug report: 0.0002 secs