CMS中的小部件的MySQL架构

Question

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:

1. SELECT all widget assignments and regions for the current page (that's one query with joins)
2. Then loop through each widget assignment and do a look up for the actual widget.

That is potentially a crap load of queries! There has to be a better way. I'm open to anything at this point.

Thanks.

Answer 1

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.