[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