Disclaimer: I'm sure this has been answered before but I cannot find anything on SO or Google.
So, I have a CMS that essentially consists of a Page, Region, Widget format. A single Page can have unlimited Regions, and a Region can have unlimited Widgets.
Right now my schema looks like this:
Table: pages
Columns: id, title, uri, ...
Table: regions
Columns: id, page_id, name, title
Table: widget_types
Columns: id, title, active
Table: widget_assignments
Columns: id, region_id, widget_type_id, data
(data represents content/settings for a specific instance of a Widget)
This works great for simple widgets. What I want to do is let every Widget Type have it's own table(s). For example, a simple content widget would have a table like this:
Table: wt_content
Columns: id, content
An example of a more complex widget (eg forums) might look something like this:
Table: wt_forums
Columns: id, ...
Table: wt_forum_threads
Columns: id, ...
Table: wt_forum_categories
Columns: id, ...
Table: wt_forum_settings
Columns: id, ...
The only way I know how to make this happen is to add a table column and a reference_id to the widget_assignments table, like this:
Table: widget_assignments
Columns: id, region_id, widget_type_id, table, reference_id
The thing I hate about this is I'll have to do a bunch of queries each page load. I'll have to:
That is potentially a crap load of queries! There has to be a better way. I'm open to anything at this point.
Thanks.
You could consider changing your approach with a just loading from browser. So don't generate the complete page in your PHP scripts but only generate a basic page with placeholders. Then let something like JQuery load the page parts.
An example for images:
http://www.appelsiini.net/projects/lazyload
This generates more requests to the server but generates very clean code. Your process would look like:
Browser -> index.php -> PHP generates a layout HTML -> browser -> browser requests specific parts needed.
For example something like this could do the trick, run it for every widget object for example.
<div class="widget" widgetUrl="http://mydomain.com/widgets/2322?optionshere">
$(this).load($(this).attr('widgetUrl'));
That would load the contents of a single widget. So your PHP application to return that could be quite simple. It only concerns the loading of the widget and not a complete page with multiple widgets.
You could do this almost fully RESTfull which might also ease the amount of code needed.
If the widgets are private you might consider adding a scope to it like:
http://mydomain.com/users/123/widgets/2322?optionshere
That way you could implement simple and fast security without making the application much more complex.