This is brilliant, a little scary, but brilliant. (note added July 2010: OK, A LOT SCARY!)
While back we built a website supporting unlimited languages and articles. For a reason unknown I decided to keep all my content in one table, index it and link my other tables to it.
This concept is quite interesting, I think it’s worth a look. Check out a sample Stored Procedure:
ALTER PROCEDURE dbo.SPSelectArticles @categoryid INT, @languageid INT, @defaultlanguageid INT, @maxarticles INT AS SET ROWCOUNT @maxarticles SELECT A.id AS id, CT.content AS title, CD.content AS description, A.publishdate AS publishdate, AC.color AS categorycolor, 1 AS iscorrectlanguage FROM articles A LEFT OUTER JOIN contentitems CT ON CT.cid = A.title_cid AND CT.languageid = @languageid LEFT OUTER JOIN contentitems CD ON CD.cid = A.description_cid AND CD.languageid = @languageid INNER JOIN articlecategories AC ON A.categoryid = AC.id WHERE A.publishdate < GETDATE() AND A.categoryid = @categoryid AND CT.content IS NOT NULL UNION ALL SELECT A.id AS id, CTD.content AS title, CDD.content AS description, A.publishdate AS publishdate, AC.color AS categorycolor, 0 AS iscorrectlanguage FROM articles A LEFT OUTER JOIN contentitems CTD ON CTD.cid = A.title_cid AND CTD.languageid = @defaultlanguageid LEFT OUTER JOIN contentitems CDD ON CDD.cid = A.description_cid AND CDD.languageid = @defaultlanguageid LEFT OUTER JOIN contentitems CT ON CT.cid = A.title_cid AND CT.languageid = @languageid LEFT OUTER JOIN contentitems CD ON CD.cid = A.description_cid AND CD.languageid = @languageid INNER JOIN articlecategories AC ON A.categoryid = AC.id WHERE A.publishdate < GETDATE() AND A.categoryid = @categoryid AND CT.content IS NULL ORDER BY A.publishdate DESC
The big idea is to pass a language id to this SP, and it does the rest. All the content on a page just changes based on the languageid. I built SP’s like this for main content, random words here and there and this one for articles.
Beauty in this beast is the fact that it looks if translation exists, and returns the default language if not found – in this case English. And it’s reasonably fast, this is written around 4 years ago!
Site has now served almost 6 million (yes, millions) page views in the past 2 years, and is still going strong.
Let me know what you think!
No related posts.
Ni hao, bienvenue, groot.
I do not know what that means.