How should I design my MYSQL table/s?
- by yaya3
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
