Multiple version webpages database design ideas

ultim · 2 Sep 2007 at 21:08

I have been given the task of design a content managed website with multiple version of each page.

Each page will have an english version and french version and the user can select between the two with links.

Each version is created by the user manually using FCKeditor, so no auto translation is involved.

I need help to figure out the best way to manage/store the pages in a database.

Do i just need two tables, one to store the page general info i.e. title and reference IDs to the french and english versions on another table which has the webpage content.

Any better solutions?

Beansprout · 2 Sep 2007 at 21:59

page
ID
Title
Author
Date

pagecontent
PageID
Language
Content

Code:

SELECT t1.Title, t1.Author, t1.Date, t2.Content
FROM page t1
INNER JOIN pagecontent t2 ON t1.ID = t2.PageId
WHERE t2.Language = 'En'

Something like that!

Tetsujin · 2 Sep 2007 at 22:29

although you'll probably want to add titles into the second table too, and you'll want a unique ID field for it aswell

Beansprout · 2 Sep 2007 at 22:43

Tetsujin said:
although you'll probably want to add titles into the second table too, and you'll want a unique ID field for it aswell

Easy, just move the title to the page content table

Dunno what you mean about a unique ID for the page content table - that's not necessary

Tetsujin · 2 Sep 2007 at 22:57

unless you ever wanted to edit one. what else are you going to use as the primary key?

Beansprout · 2 Sep 2007 at 23:50

Tetsujin said:
unless you ever wanted to edit one. what else are you going to use as the primary key?

Page ID and language.

Didn't put in PK/FK for want of confusing OP