我应该如何设计我的MYSQL表？ [英] How should I design my MYSQL table/s?

问题描述

我为一个使用一个projects表的架构师构建了一个非常基本的php / mysql站点。
该网站展示了他所从事的各种项目。

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'.

页面包含...

• 一张图片


• 一个文字


• 一个图像和一个文本。

每个页面都可以使用三种布局之一。

Each page could use one of three layouts.

由于每个项目当前没有超过4个文本（一个非常危险的假设），我已经展开原来的桌子适应一切。

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;

在尝试了解有关MYSQL表结构化的更多信息时，我刚刚读取一个规范化介绍和关系数据库理论中五个正常表单的简单指南。我要继续阅读！
提前感谢

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

推荐答案

规范化是你的朋友。

你需要移动到一个关系双表设计。

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.

如果客户端回来并说每个页面都可以有0-N个图像，那么您需要一个第三个表，其中包含一个外键 page_id （就像pages表有一个 project_id 外键）

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)

这篇关于我应该如何设计我的MYSQL表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！