[thelist] pretty databases vs. fast databases

Emma Jane Hogbin emmajane at xtrinsic.com
Sun Mar 9 14:17:01 CST 2003


I've set up my database so that it will very easily handle translations of
text. I like the database design. Unfortunately everything is in its own
home and queries now look like this:

select
	meta_articles.id as id,
	content.en_title as subject, <--- language changes here by locale
		e.g. fr_title, it_title, ru_title (etc).
		The "content" table holds titles and content for virtually
		all text in the site (comments, articles, categories). It
		means that when a new locale is added only one table is
		updated and queries should be very easy to update. Simply
		change the locale for **_title and **_text. There's also
		an option for "personal annotations" in the locale/content
		table. I also have a setting for personal/public at the
		article/comment level and am not entirely sure which I'll
		use.
	meta_articles.created as created,
	meta_comments.updated as updated,
	meta_comments.author as who
from meta_articles
left join meta_comments on meta_comments.id = meta_articles.last_comment_id
left join cats_article on cats_article.article = meta_articles.id <--
		allows multilingual content or content sorted by language
		(one "article" can go into multiple categories/forums)
left join content on meta_articles.content = content.id
left join login on meta_comments.author = login.logid
where cats_article.cat = 2
order by meta_articles.created DESC


As I mentioned, I'm really pleased with this database; however, it's going
to chug under any kind of load. (NOTE: this is going to be a VERY low load
site to start -- i.e. under 100 people.) The way things are built I'm
hoping that a publishing system would very easily tie in as the traffic
increases.

Here's my question: how do people feel about "wrecking" database
normalization and sticking (e.g.) author names into the meta_comments
table in addition to the author ID. It would reduce the query by one join.

emma

--
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]



More information about the thelist mailing list