Page / Email content schema

Question

I am creating a user content website similar to yelp. Site will support multi language in 15 different languages. Need some advice on storing system content, that is:

• Page content (all the default page text and menu items)
• Page attributes (Title bar, meta tags like keywords, description, etc)
• Email content (Subject, header, footer - both html and text versions)

For performance purpose, I feel only 1 table is to be used for all of this with colunms like: ID, Eng, Fr, Es, Gr,... where each column is a language and ID is the keyword used to identify the word. Based on user's language session it will pull in the matching words to load.

QUESTIONS:

1. Will this design scale as system content increases to thousands of pages?
2. Easy to edit text?
3. Performance impact?
4. Any better designs out there based on this needs?
5. Any other type of system content do i need to maintain?
6. Also for text like page title bar which are dynamic - like on the profile page it will be "User's full name - Site name) all those can be done at DB or code level?
7. Biggest concern - the lookup values such as city names, category names, etc. they reside in their own lookup table. So to translate them do i add language columns to those tables or cpy these lookup values to the content table only?

Platform: PHP / MySQL

Answer 1

Q:Will this design scale well as system content increases to thousands of pages? Easy to edit text? Performance impact? Any better designs out there based on this needs? Any other type of system content do i need to maintain?

A:All applications respond differently to large load so there isn't any straight answer.As long as you are reducing data redundancy and keeping it DRY(dont repeat yourself). Performance while query and code is better.

Q: Also for text like page title bar which are dynamic - like on the profile page it will be "User's full name - Site name) all those can be done at DB or code level?

A: You can have it in DB but you can make cache system. So you page dont query data until there is not change in db. In this way it look like static page but dynamic at same time.

At end , i would sugesset you to use some standard framework like zend, codeigniter, cakePHP. AS they have better solution of large scale projects on

• performance
• security
• documentation
• community
• alot of pre made and test solutions required for any project

So using framework ,is alway safe but it dont mean having project from scratch are not.