0

In my previous work station, I saw them have a table design across all website they have worked on displaying editor content like this:

column|page_id| title |content_1|... |content_n| lang  |update_date|...
-----------------------------------------------------------------------
row   |   1   |varchar|   text  |text|  text   |tinyint|  datetime |
......

where content_n column are determined by the max editor it overall has on that website.

For an example: If the website generally has 1 editable content per page but in addition, there is one page that has 4 editable content, they will have 4 content_n column.

The problem(imo):

For pages that have only 1 editable content area, only content_1 will be filled, and other column content_n will be empty. In addition, with multiple languages involve, those empty field add up.

My question:

  1. Is this a good design of choice?
  2. Wouldn't those empty column create unnecessary overhead?
  3. What would be an improved table design when have a table such as this?

My thought :

I will probably merge all the content and title into one single column(mediumtext/longtext) and use json_encode/array to differentiate the content.

they usemysql(MYISAM) for all database engineer, and I prefer Innodb..if that is matter

0

Do not build arrays as columns. Instead, use extra tables. If the different content_* are differeng languages, have another table with page_id (for JOINing), content, and lang. Probably the PRIMARY KEY would be (page_id, lang).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.