Hi all I decided to try and write a lightweight cms in rails but I''m struggling with the database side of things All CMS content is made up of a node. There are different types of nodes e.g : pages, paragraphs, images, widgets ... each node can have one parent node (or no parent) and each node can have zero or more child nodes. Obviously, not all node types contain the same information. A paragraph node contains text, an image node contains the filename of a corresponding image file, a page node has fields which are of no use to other nodes like title, template and stylesheet. It''s therefore impractical to have one table which tries to accommodate all node types. One solution would be to have a seperate table for each node type, and give each table a set of columns common to all nodes. This wouldn''t work because it would be difficult to track child/parent relationships between nodes and also for the system to work with all nodes in a central way (e.g. Getting a list of all root nodes (nodes without a parent) would involved querying all node tables for such nodes, which means hardcoding/or storing the table names somewhere) I''m thinking of setting it up so there''s a nodes table holding the information common to all nodes: id unique node id created_dts date/time stamp of when this node was created updated_dts date/time stamp of when this node was updated owner_id user id of the user that created this node parent_id id of this node''s parent node (-1 for none) a seperate table holds data specific to the node type, e.g. For a page node : id unique page id title page title stylesheet which stylesheet to use when rendering the page the node table would contain two additional fields to link it to the pages table : ext_table table where extra info about this node can be found ext_id id of the record in ext_table which this node belongs to The page table would have an additional field linking it to the nodes table: node_id id of this node''s record in the nodes table Rails implementation --------------------- In the node model: belongs_to :page, :condition => “ext_table = page”, :foreign_key => “ext_id” in the page model: has_one :node do you guys think this is a good way to implement the system? This is how I was doing it on PHP, but I''m not sure if there would be a better way of implementing it in rails (assuming that my database design isn''t wrong) thanks for any input dylan
Hi Dylan, We are building a similar CMS system here and encountered some of the same challenges and came to some of the same conclusions. The main difference seems to be that we are approaching our system more from the top down in terms of having pages, articles, etc. in the main table. Where you are looking at it more of a bottom up of having the nodes be the primary item and then having those linked off to make the different elements. Not sure that either method is better than the other, but I''ll try to explain what we are doing and maybe it can/will help. (I apologize as my response ended up even longer than your original). We''ve set our system up so there is one main content table that holds the top level elements (pages, articles, recipes, etc.). We differentiate between the different elements through the use of single table inheritance. That table includes these fields: id name created_on created_by last_updated last_update_by additional_table Then we have a fields table that holds all the different fields. The important columns are: id content_id - foreign key linking it back to the main content table name field_type - This specifies what type of field it is (shorttext, longtext, htmltext, image, file, flash, etc.) value - This is a text field and the value for the field is entered here using text separators so for an image the value might be path:::/the/path/image.jpg|||width:::30|||height:::100|||alt:::an image Then in the code we have objects that match each of the field_types in the table that know how to parse the field and display it (for editing or displaying). Each of those objects can have special functionality based on what that field type needs. We choose this method as it then keeps all the content in one table instead of trying to join/group a bunch of different tables to display a single page. It is also very easy to add a new field type, and as soon as you add the field type it is available to all the different content in the system without having to update any code other than defining the new field object. I should also mention that in the main content table we also have a field for an additional_table that can be used for holding additional information, this is especially useful if you want to do order by clauses. For example for news articles generally you will want to order them by publish date. If the publish date was a field in the main fields table it would be pretty difficult to do an order by with it. So for that we would create an articles table that would be linked through the content_id field and have a column for publish_date. Setting up the model relationships looks like this then in our system: class Content < ActiveRecord::Base has_many :fields class Article < Content has_one :article_attrs class Page < Content class Template < Content class Field < ActiveRecord::Base belongs_to :content Don''t know if that really helps answer your questions at all, but hopefully it helps a bit at least. --James At 10:10 AM 2/3/2005, you wrote:>Hi all > >I decided to try and write a lightweight cms in rails but I''m struggling >with the database side of things > >All CMS content is made up of a node. There are different types of nodes >e.g : pages, paragraphs, images, widgets ... each node can have one parent >node (or no parent) and each node can have zero or more child nodes. > >Obviously, not all node types contain the same information. A paragraph >node contains text, an image node contains the filename of a corresponding >image file, a page node has fields which are of no use to other nodes like >title, template and stylesheet. > >It''s therefore impractical to have one table which tries to accommodate >all node types. One solution would be to have a seperate table for each >node type, and give each table a set of columns common to all nodes. This >wouldn''t work because it would be difficult to track child/parent >relationships between nodes and also for the system to work with all nodes >in a central way (e.g. Getting a list of all root nodes (nodes without a >parent) would involved querying all node tables for such nodes, which >means hardcoding/or storing the table names somewhere) > >I''m thinking of setting it up so there''s a nodes table holding the >information common to all nodes: > >id unique node id >created_dts date/time stamp of when this node was created >updated_dts date/time stamp of when this node was updated >owner_id user id of the user that created this node >parent_id id of this node''s parent node (-1 for none) > >a seperate table holds data specific to the node type, e.g. For a page node : > >id unique page id >title page title >stylesheet which stylesheet to use when rendering the page > >the node table would contain two additional fields to link it to the pages >table : > >ext_table table where extra info about this node can be found >ext_id id of the record in ext_table which this node belongs to > >The page table would have an additional field linking it to the nodes table: >node_id id of this node''s record in the nodes table > > >Rails implementation >--------------------- >In the node model: > >belongs_to :page, > :condition => "ext_table = page", > :foreign_key => "ext_id" > >in the page model: > >has_one :node > > >do you guys think this is a good way to implement the system? This is how >I was doing it on PHP, but I''m not sure if there would be a better way of >implementing it in rails (assuming that my database design isn''t wrong) > >thanks for any input >dylan >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >http://lists.rubyonrails.org/mailman/listinfo/rails
James Punteney wrote:> We''ve set our system up so there is one main content table that holds > the top level elements (pages, articles, recipes, etc.). We > differentiate between the different elements through the use of single > table inheritance. > > Then we have a fields table that holds all the different fields. > > At 10:10 AM 2/3/2005, you wrote: >> It''s therefore impractical to have one table which tries to >> accommodate all node types. One solution would be to have a seperate >> table for each node type, and give each table a set of columns common >> to all nodes. This wouldn''t work because it would be difficult to >> track child/parent relationships between nodes and also for the system >> to work with all nodes in a central way (e.g. Getting a list of all >> root nodes (nodes without a parent) would involved querying all node >> tables for such nodes, which means hardcoding/or storing the table >> names somewhere) >> dylanJames, your approach is very flexible, but queries and updates involving more than one attribute are expensive. If you treat the content fields as a "blob" of data, consider serializing the Hash to a single database column in your Content table: class Content < ActiveRecord::Base serialize :fields def before_create self.fields ||= {} end end You can play with method_missing magic to allow content.foo = 4 to set content.fields[''foo''] = 4. Also, note that using "additional tables" (eg, Article has_one :article_attrs) is equivalent to Dylan''s approach. Dylan, you have discovered class table inheritance. Since Active Record doesn''t support it natively, you do it by hand using single table inheritance and composition: # create table nodes (id serial, type varchar(64)); class Node < ActiveRecord::Base; end # create table pages (id serial, node_id integer references nodes(id)); class PageData < ActiveRecord::Base def self.table_name ''pages'' end end # Lives in nodes table. class Page < Node has_one :page_data end You may use method_missing on page to delegate to page_data. The disadvantage here is that pulling up a full page takes two queries. Class table inheritance When Active Record supports class table inheritance, it will join all the tables in the inheritance hierarchy and pull the whole record in one query. jeremy
thanks both for your input. I''m relatively new to programming and databases so the info you provided is a little over my head but I get the general gist of it.> Dylan, you have discovered class table inheritance. Since Active Record > doesn''t support it natively, you do it by hand using single table > inheritance and composition: > > # create table nodes (id serial, type varchar(64)); > class Node < ActiveRecord::Base; end > > # create table pages (id serial, node_id integer references nodes(id)); > class PageData < ActiveRecord::Base > def self.table_name > ''pages'' > end > end > > # Lives in nodes table. > class Page < Node > has_one :page_data > end > > You may use method_missing on page to delegate to page_data. The > disadvantage here is that pulling up a full page takes two queries. > Class table inheritance When Active Record supports class table > inheritance, it will join all the tables in the inheritance hierarchy > and pull the whole record in one query. >jeremy, I set up my models the way you''ve shown but when I try to use the Page model I get ''superclass mismatch for class Page'' . any idea what could be causing this? thanks dylan
* Dylan <b9704-Ku7NbOBBH+dBDgjK7y7TUQ@public.gmane.org> [2005-02-03 10:11]:> All CMS content is made up of a node. There are different types of nodes > e.g : pages, paragraphs, images, widgets ... each node can have one > parent node (or no parent) and each node can have zero or more child nodes.As an aside, this CMS, ''Krang'' (an offshoot of another one called Bricolage) uses a similar structure - everything is an ''element''. It''s perl but the DB schema might be useful, as these CMSs have been developed over some years now. They do build static pages though, so they may not be performance-oriented enough unless you heavily cache... here''s a semi-recent DB schema: http://krang.sourceforge.net/docs/database_schema.gif -- ______________________________ toddgrimason*todd-AT-slack.net