Forums / Suggestions / Ideal database scheme for eZPublish

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