I''m working on a CMS for a client who wants a database schema like
this:
Table `objects`:
id int primary key not null auto_increment,
type
enum(''Article'',''News'',''Review'')
not null,
postdate datetime not null,
author_id int not null,
title varchar(100) not null,
slug varchar(50) not null,
body text not null
Table `articles`:
object_id int primary key not null,
summary text not null
Table `news`:
object_id int primary key not null,
source_name varchar(100) not null,
source_url varchar(150) not null
Table `reviews`:
object_id int primary key not null,
price decimal(5,2) not null,
summary text not null,
lead text not null,
comments text not null
This, of course, is abbreviated, but you get the idea. Articles, news,
and reviews have rows in the `objects` table and all their extra data
stored in separate tables. This is to make it easy (and hopefully
fast) to perform fulltext searchs of everything at once.
I have been debating whether I should argue for putting all the fields
into one table (which I *really* do not want to do) and use Rails''
STI, or have one model, Object, and use explicit :joins: when I need
the extra information.
For a site that will have potentially thousands of rows in this
`objects` table (we are using MySQL), which is the best programming
route to take?
Sincerely,
Tom Reinhart
tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org
http://AllTom.com/
Our ASP.Net CMS uses a very similar model, and it works great. SQL Server is also doing a lot more on the backend to construct simple ordered and paged queries. To be clear, I''m talking about using Joins as needed. On 5/3/05, Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m working on a CMS for a client who wants a database schema like this: > > Table `objects`: > id int primary key not null auto_increment, > type enum(''Article'',''News'',''Review'') not null, > postdate datetime not null, > author_id int not null, > title varchar(100) not null, > slug varchar(50) not null, > body text not null > > Table `articles`: > object_id int primary key not null, > summary text not null > > Table `news`: > object_id int primary key not null, > source_name varchar(100) not null, > source_url varchar(150) not null > > Table `reviews`: > object_id int primary key not null, > price decimal(5,2) not null, > summary text not null, > lead text not null, > comments text not null > > This, of course, is abbreviated, but you get the idea. Articles, news, > and reviews have rows in the `objects` table and all their extra data > stored in separate tables. This is to make it easy (and hopefully > fast) to perform fulltext searchs of everything at once. > > I have been debating whether I should argue for putting all the fields > into one table (which I *really* do not want to do) and use Rails'' > STI, or have one model, Object, and use explicit :joins: when I need > the extra information. > > For a site that will have potentially thousands of rows in this > `objects` table (we are using MySQL), which is the best programming > route to take? > > Sincerely, > > Tom Reinhart > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > http://AllTom.com/ > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- rick http://techno-weenie.net
Drop the objects table and: 1. join the other 3 when you need to search or 2. do 3 searches and present the results by object type 3. or build a search term table. The rest of the time (99% of the time) you''ll save youself from joining objects with the other tables. Don''t slow all your other queries just to speed searches. Rule of thumb, you should have a very good reason to set up seperate tables with 1:1 relationships most of the time it just adds overhead. On 5/3/05, Rick Olson <technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Our ASP.Net <http://ASP.Net> CMS uses a very similar model, and it works > great. SQL > Server is also doing a lot more on the backend to construct simple > ordered and paged queries. > > To be clear, I''m talking about using Joins as needed. > > On 5/3/05, Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I''m working on a CMS for a client who wants a database schema like this: > > > > Table `objects`: > > id int primary key not null auto_increment, > > type enum(''Article'',''News'',''Review'') not null, > > postdate datetime not null, > > author_id int not null, > > title varchar(100) not null, > > slug varchar(50) not null, > > body text not null > > > > Table `articles`: > > object_id int primary key not null, > > summary text not null > > > > Table `news`: > > object_id int primary key not null, > > source_name varchar(100) not null, > > source_url varchar(150) not null > > > > Table `reviews`: > > object_id int primary key not null, > > price decimal(5,2) not null, > > summary text not null, > > lead text not null, > > comments text not null > > > > This, of course, is abbreviated, but you get the idea. Articles, news, > > and reviews have rows in the `objects` table and all their extra data > > stored in separate tables. This is to make it easy (and hopefully > > fast) to perform fulltext searchs of everything at once. > > > > I have been debating whether I should argue for putting all the fields > > into one table (which I *really* do not want to do) and use Rails'' > > STI, or have one model, Object, and use explicit :joins: when I need > > the extra information. > > > > For a site that will have potentially thousands of rows in this > > `objects` table (we are using MySQL), which is the best programming > > route to take? > > > > Sincerely, > > > > Tom Reinhart > > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > > http://AllTom.com/ > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 5/3/05, Barry Walker <barryjr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Drop the objects table and: > > join the other 3 when you need to search or > do 3 searches and present the results by object type > or build a search term table. > > The rest of the time (99% of the time) you''ll save youself from joining > objects with the other tables. Don''t slow all your other queries just to > speed searches. Rule of thumb, you should have a very good reason to set up > seperate tables with 1:1 relationships most of the time it just adds > overhead.Won''t searches take that much longer since for each one there will be three fulltext indices being browsed through every time? That''s the only complaint I can think of for dropping the objects table (besides the fact that they want an objects table :) ). Sincerely, Tom Reinhart tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org http://AllTom.com/
Another aspect is that if you store all objects in one table, and use single-table inheritance, you can then retrieve any kind of content by simply passing an id (or slug, date, or whatever unique identifier you can think of). What you get back is an object of the "right" kind, provided that you have a "type" field as well. If you on the other hand don''t use single-table inheritance, you can''t just pass an id, you need to pass an id to the right Model, and to do that you need to know which Model to pass the id to, which most likely will clutter up or constrain your URLs (instead of /anypath/you/want/123 you need to use /anypath/you/want/contenttype/123 or whatever). If someone knows of a good way to get around this problem, please feel free to chime in. Regards, Tomas Jogin On 5/3/05, Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m working on a CMS for a client who wants a database schema like this: > > Table `objects`: > id int primary key not null auto_increment, > type enum(''Article'',''News'',''Review'') not null, > postdate datetime not null, > author_id int not null, > title varchar(100) not null, > slug varchar(50) not null, > body text not null > > Table `articles`: > object_id int primary key not null, > summary text not null > > Table `news`: > object_id int primary key not null, > source_name varchar(100) not null, > source_url varchar(150) not null > > Table `reviews`: > object_id int primary key not null, > price decimal(5,2) not null, > summary text not null, > lead text not null, > comments text not null > > This, of course, is abbreviated, but you get the idea. Articles, news, > and reviews have rows in the `objects` table and all their extra data > stored in separate tables. This is to make it easy (and hopefully > fast) to perform fulltext searchs of everything at once. > > I have been debating whether I should argue for putting all the fields > into one table (which I *really* do not want to do) and use Rails'' > STI, or have one model, Object, and use explicit :joins: when I need > the extra information. > > For a site that will have potentially thousands of rows in this > `objects` table (we are using MySQL), which is the best programming > route to take? > > Sincerely, > > Tom Reinhart > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > http://AllTom.com/ > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
One table promotes: /site.com/object/10478 Multi tables promotes: /site.com/author/earnest+hemmingway /site.com/article/technical+writing /site.com/news/2005-05-04 I''ll take the latter. Your application may prefer the former. On 5/4/05, Tomas Jogin <tomasj-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Another aspect is that if you store all objects in one table, and use > single-table inheritance, you can then retrieve any kind of content by > simply passing an id (or slug, date, or whatever unique identifier you > can think of). What you get back is an object of the "right" kind, > provided that you have a "type" field as well. > > If you on the other hand don''t use single-table inheritance, you can''t > just pass an id, you need to pass an id to the right Model, and to do > that you need to know which Model to pass the id to, which most likely > will clutter up or constrain your URLs (instead of > /anypath/you/want/123 you need to use > /anypath/you/want/contenttype/123 or whatever). > > If someone knows of a good way to get around this problem, please feel > free to chime in. > > Regards, > Tomas Jogin > > On 5/3/05, Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I''m working on a CMS for a client who wants a database schema like this: > > > > Table `objects`: > > id int primary key not null auto_increment, > > type enum(''Article'',''News'',''Review'') not null, > > postdate datetime not null, > > author_id int not null, > > title varchar(100) not null, > > slug varchar(50) not null, > > body text not null > > > > Table `articles`: > > object_id int primary key not null, > > summary text not null > > > > Table `news`: > > object_id int primary key not null, > > source_name varchar(100) not null, > > source_url varchar(150) not null > > > > Table `reviews`: > > object_id int primary key not null, > > price decimal(5,2) not null, > > summary text not null, > > lead text not null, > > comments text not null > > > > This, of course, is abbreviated, but you get the idea. Articles, news, > > and reviews have rows in the `objects` table and all their extra data > > stored in separate tables. This is to make it easy (and hopefully > > fast) to perform fulltext searchs of everything at once. > > > > I have been debating whether I should argue for putting all the fields > > into one table (which I *really* do not want to do) and use Rails'' > > STI, or have one model, Object, and use explicit :joins: when I need > > the extra information. > > > > For a site that will have potentially thousands of rows in this > > `objects` table (we are using MySQL), which is the best programming > > route to take? > > > > Sincerely, > > > > Tom Reinhart > > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > > http://AllTom.com/ > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 5/5/05, Barry Walker <barryjr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> One table promotes: > > /site.com/object/10478 > > Multi tables promotes: > > /site.com/author/earnest+hemmingway > /site.com/article/technical+writing > /site.com/news/2005-05-04Don''t confuse your controllers with your model, there''s no reason you can''t implement a news controller which does Object.find(:first, :contions => [relevant conditions]) A controller is just a way to get references to models and manipulate them. It''s perfectly fine to have one controller manipulate lots of different models, and have your models get manipulated by lots of controllers. In fact it''s *desirable*.> I''ll take the latter. Your application may prefer the former. > > > > On 5/4/05, Tomas Jogin <tomasj-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Another aspect is that if you store all objects in one table, and use > > single-table inheritance, you can then retrieve any kind of content by > > simply passing an id (or slug, date, or whatever unique identifier you > > can think of). What you get back is an object of the "right" kind, > > provided that you have a "type" field as well. > > > > If you on the other hand don''t use single-table inheritance, you can''t > > just pass an id, you need to pass an id to the right Model, and to do > > that you need to know which Model to pass the id to, which most likely > > will clutter up or constrain your URLs (instead of > > /anypath/you/want/123 you need to use > > /anypath/you/want/contenttype/123 or whatever). > > > > If someone knows of a good way to get around this problem, please feel > > free to chime in. > > > > Regards, > > Tomas Jogin > > > > On 5/3/05, Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > I''m working on a CMS for a client who wants a database schema like this: > > > > > > Table `objects`: > > > id int primary key not null auto_increment, > > > type enum(''Article'',''News'',''Review'') not null, > > > postdate datetime not null, > > > author_id int not null, > > > title varchar(100) not null, > > > slug varchar(50) not null, > > > body text not null > > > > > > Table `articles`: > > > object_id int primary key not null, > > > summary text not null > > > > > > Table `news`: > > > object_id int primary key not null, > > > source_name varchar(100) not null, > > > source_url varchar(150) not null > > > > > > Table `reviews`: > > > object_id int primary key not null, > > > price decimal(5,2) not null, > > > summary text not null, > > > lead text not null, > > > comments text not null > > > > > > This, of course, is abbreviated, but you get the idea. Articles, news, > > > and reviews have rows in the `objects` table and all their extra data > > > stored in separate tables. This is to make it easy (and hopefully > > > fast) to perform fulltext searchs of everything at once. > > > > > > I have been debating whether I should argue for putting all the fields > > > into one table (which I *really* do not want to do) and use Rails'' > > > STI, or have one model, Object, and use explicit :joins: when I need > > > the extra information. > > > > > > For a site that will have potentially thousands of rows in this > > > `objects` table (we are using MySQL), which is the best programming > > > route to take? > > > > > > Sincerely, > > > > > > Tom Reinhart > > > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > > > http://AllTom.com/ > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-- Cheers Koz
On 5/4/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 5/5/05, Barry Walker <barryjr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > One table promotes: > > > > /site.com/object/10478 > > > > Multi tables promotes: > > > > /site.com/author/earnest+hemmingway > > /site.com/article/technical+writing > > /site.com/news/2005-05-04 > > Don''t confuse your controllers with your model, there''s no reason > you can''t implement a news controller which does Object.find(:first, > :contions => [relevant conditions])Took the words from my mouth. :) I described my qualms with having the objects table to the client, and I was told that I had it all wrong! Here is what they had meant originally: Have the `objects` table, and then another `attributes` table with one field for each data type (IntValue, TextValue, BlobValue, DateValue, etc.). Each object (article, review, user, session, etc.) has a stub row in the objects table, and all of its attributes are tacked on as rows in the `attributes` table, one attribute per row (leave all the fields empty except the one that matches the type of the attribute). Needless to say, I''m even more opposed to this idea... Thanks for all the help, guys. I think I can handle it from here. :) Sincerely, Tom Reinhart tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org http://AllTom.com/
In article <326364c205050421452cd745bb-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org>, alltom- Re5JQEeQqe8AvxtiuMwx3w-XMD5yJDbdMReXY1tMh2IBg@public.gmane.org says...> Have the `objects` table, and then another `attributes` table with one > field for each data type (IntValue, TextValue, BlobValue, DateValue, > etc.). Each object (article, review, user, session, etc.) has a stub > row in the objects table, and all of its attributes are tacked on as > rows in the `attributes` table, one attribute per row (leave all the > fields empty except the one that matches the type of the attribute). > > Needless to say, I''m even more opposed to this idea...Whoooooooooooooah. Yeah. Good luck talking them out of that.. sounds like you''ll need it. Why not say, "I think that''s actually not abstract enough; it still assumes a computer-based knowledge system. Better to store just two fields: The name of the object, and the cell phone of a guy who can describe it." -- Jay Levitt | Wellesley, MA | I feel calm. I feel ready. I can only Faster: jay at jay dot fm | conclude that''s because I don''t have a http://www.jay.fm | full grasp of the situation. - Mark Adler