How should I design my MYSQL table/s?

Question

I built a really basic php/mysql site for an architect that uses one 'projects' table. The website showcases various projects that he has worked on.

Each project contained one piece of text and one series of images.

Original projects table (create syntax):

CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL auto_increment,
  `project_name` text,
  `project_text` text,
  `image_filenames` text,
  `image_folder` text,
  `project_pdf` text,
  PRIMARY KEY  (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

The client now requires the following, and I'm not sure how to handle the expansions in my DB. My suspicion is that I will need an additional table.

Each project now have 'pages'.

Pages either contain...

• One image
• One "piece" of text
• One image and one piece of text.

Each page could use one of three layouts.

As each project does not currently have more than 4 pieces of text (a very risky assumption) I have expanded the original table to accommodate everything.

New projects table attempt (create syntax):

CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_name` text,
  `project_pdf` text,
  `project_image_folder` text,
  `project_img_filenames` text,
  `pages_with_text` text,
  `pages_without_img` text,
  `pages_layout_type` text,
  `pages_title` text,
  `page_text_a` text,
  `page_text_b` text,
  `page_text_c` text,
  `page_text_d` text,
  PRIMARY KEY (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

In trying to learn more about MYSQL table structuring I have just read an intro to normalization and A Simple Guide to Five Normal Forms in Relational Database Theory. I'm going to keep reading! Thanks in advance

Answer 1

Normalization is your friend.

You'll want a to move to a relational two-table design.

CREATE TABLE projects (
   project_id int not null primary key auto_increment,
   project_name varchar(128),
   -- ...
);

CREATE TABLE pages (
   page_id int not null primary key auto_increment,
   project_id int not null, 
   pagetext text,
   image varchar(128), 
   -- ...
);

Now each project can have any number of pages.

If the client then comes back and says "Each page can have 0-N images", you'd want a third table, which contains a foreign key page_id (just like the pages table has a project_id foreign key)