[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