Advice on rethinking my database structure

[eluser]mvdg27[/eluser]
Hi guys,

I've been developing a CMS for a while now, and I'm at the point of trying to make my code and database structure a bit more structured and better extensible. So I'm working on an experimental version and I'd appreciate some advice, especially on the database structure.

Before I start let's explain, really simplistic, how the CMS works now (on the front-end side that is):

I have a table name 'sections', which lists all the different sections in the website. A section is for example 'English website', 'Spanish website', 'German website', 'Forum', 'Blog'. The table sections contains the URL of the section and the specific module (= website-module, forum-module etc.) for that section.

Then for each section, I have a separate table (e.g. section1, section2, etc.), which contains the pages and all information about these individual pages (including the item url). Each separate section table, contains some columns that are similar, but different modules dictate different columns to be necessary. A simple example: the section table for a blog module needs an extra column to indicate if comments are closed or not.

Ok. This is short is how I have set up the structure now. Recently I've started playing around with the DMZ library, which gave me some ideas to restructure the whole thing.

Now my idea is to minimize the number of seperate tables that I need, and store all the pages (for website module, blog module etc.) in one and the same table. Then I would add a relations table that links the section to the specific page. Something like this:

Code:

Table: sections
id#name#url#module

Table: items
id#parent#age#label#url

Table: items_sections
id#section_id#item_id

So far I have this working, and I like the setup. But now the important question rises: how do I add the module-specific columns in this structure? To stay with the previous example: where do I store the 'comments_closed' information for Blog items? And where do I store the reference to a photo and a thumbnail in the case of a photoalbum?

A few ideas I'm having now:

- simply add all the possible columns to the items-table. For some pages these columns simply won't be used. Kind of an ugly hack in my opinion.

- add a seperate table for each existing module (not for every section), with the extra columns and a field item_id matching the id of the page in the table items. Sounds like a usefull setup to me, although it scatters information around over different tables now

- replace the items table for one specific table per module, each with a seperate relations table for the section. This setup however implies that still I don't have a single easy to access table with all the different pages in various sections. Something I plan to use for making a global module for restricting view access for different logged-in users.

I hope my explanation so far is clear enough and I'm looking forward to seeing some advice. How would you guys handle something like this?

Thanks in advance! -Michiel

[eluser]tonanbarbarian[/eluser]
i would not add the module specific columns
instead have a medium or long text field in which you store serialised version of the module specific content.

this works well if you never need to search the content based on the module data, which it seems you would not really need to do because the module data is more about how to display

not really sure you need the relation table if the items section is a 1-1 relationship

[eluser]mvdg27[/eluser]
Thanks for your reply. Your suggestion for storing serialized module specific content is a good idea, but I'm afraid that's going to make the system very inflexible. In the example I gave you, indeed the extra information is only for displaying, but I can imagine situations where I do want the content to be searchable.

I guess all that's left is adding a separate table for each module and matching it either directly or with a relationship table.

Thanks for your input, if anyone else has some extra ideas on this, please do let me know!

[eluser]BigDon[/eluser]
mvdg27,

I would add an extra column to the "Items" table, "closed" with a boolean value.

In your controller, check for the status of the item before presenting the data. If an items closed value = TRUE, then turn off commenting, set your notification, etc..

[eluser]mvdg27[/eluser]
@bigdon: thanks for your reply, but your 'solution' is not really the issue here. Your suggestion of adding extra columns is exactly what I'm wanting to prevent. I'm looking for a smart and flexible solution. Thanks though ..

[eluser]BigDon[/eluser]
[quote author="mvdg27" date="1290479159"]@bigdon: thanks for your reply, but your 'solution' is not really the issue here. Your suggestion of adding extra columns is exactly what I'm wanting to prevent. I'm looking for a smart and flexible solution. Thanks though ..[/quote]


And you are implying?????

[eluser]mvdg27[/eluser]
Not implying anything .. I'm thanking you for taking the time to answer my question and simply saying that I was looking for a different type of solution. That's all ..

[eluser]BigDon[/eluser]
[quote author="mvdg27" date="1290480408"]Not implying anything .. I'm thanking you for taking the time to answer my question and simply saying that I was looking for a different type of solution. That's all ..[/quote]

My apologies then... I mistook your statement "smart and flexible" to mean this was a pretty dumb idea!

As I understand your explanation of the problem you are attempting to solve I can see no other means of determining the status of a post without having stored that status somewhere. Adding another table would not seem reasonable to me because you would then have the overhead at minimum 3 additional fields and the table. Whereas, the addition of the column would add very little overhead and could be easily retrieved during the query to retrieve the records.

Or maybe I'm just not understanding the issue completely...

[eluser]mvdg27[/eluser]
I don't think your idea was dumb no, it's actually the way my database structure is set up now. It's kind of the standard way of doing things, I guess.

The thing is that I'm looking for a way to make my structure more flexible. The 'closed'-property is just an example of an extra property that could exist for a blog module. But a photo album might need different fields, as would a forum module.

I'm looking for a smart and flexible way to have one database with all the items of various modules, and store the other properties (like closed, or reference to a photo and a thumbnail etc.) somewhere as well, without having to add several columns to the items-table, which for some items might not be used at all.

[eluser]BigDon[/eluser]
I think I am beginning to understand a little better now.... I still would go with the Boolean field on the pages index. However, you could add a separate table that would hold only those "occasional" fields such as photos, thumbnails, links, etc., and link that table's data row to the individual pages they are associated with. That table would be smaller, used less often, and would only need to be linked back to the original post via a relational link.

Your controller would just need to include the extra logic to check if there is a linked field in the other database....

Is this sort of headed in the direction you wanted to go????