寻找一种使用PHP和MySQL来存储表的更好方法 [英] Looking for a better way to store a table using PHP and MySQL

问题描述

我是PHP和MySQL的新手，需要解决我面临的问题.我试图创建一个更干净的表，然后执行以下操作:

I am new to PHP and MySQL and require help with an issue I am facing. I am trying to create a table that is cleaner then the following:

表格示例

项目|零件|数量

Item1 | 123 | 2

Item1 |123 |2

项目1 | 124 | 2

Item1 |124 |2

Item2 | 123 | 1

Item2 |123 |1

Item2 | 125 | 3

Item2 |125 |3

我可以使用普通表来执行此操作，但是我可以设想数据库中包含大量重复行和重复数据.例如，我多次使用Item1来标识每个零件.

I can do this with a normal table but I can envision the database having tons of repeat rows with duplicate data. For example I used Item1 multiple times to identify each part.

我想知道是否有一种更干净的方法来将这些数据存储在数据库中?我将使用PHP将数据存储到MySQL中.我还希望使项目"列具有唯一性，但就目前而言，不能这样做.

I was wondering if there was a cleaner way to store this data in my database? I will be using PHP to store the data into MySQL. I am also looking to make the Item column unique but as it stands, can not do this.

我研究了序列化，联接以及数组，但是我不知道如何使它们变得合适，所以我想在这里问.最终结果将是一个PHP报告，内容为:

I looked into serialize, join as well as an array but I couldn't figure out how to make it all fit so I thought I would ask here. The end results would be a PHP report that says:

第1项使用以下部分

第123部分:数量2

Part 123 : Quantity 2

第124部分:数量2

Part 124 : Quantity 2

第2项使用以下部分

第123部分:数量1

Part 123: Quantity 1

第125部分:数量3

Part 125: Quantity 3

任何帮助将不胜感激.

推荐答案

您的商品和零件之间存在多对多的关系.

You have a many-to-many relation between your items and your parts.

因此，您需要3个表(item，part和link_item_part).

So you need 3 tables (item, part, and link_item_part).

您的项目表将有一个ID作为主键.您的零件表也是如此.您的link_item_part表将具有由两个外键组成的复合主键，一个在项目上，另一个在零件上.

Your item table will have an id as a primary key. Same goes for your part table. Your link_item_part table will have a compound primary key build from two foreign keys one on item, the other one on part.

-- item table
| id | name   |
+----+--------+
|  1 | Item 1 |
|  2 | Item 2 |

-- part table
| id | name |
+----+------+
|  1 |  123 |
|  2 |  124 |
|  3 |  125 |

-- link_item_part
| item_id | part_id | quantity |
+---------+---------+----------+
|       1 |       1 |        2 |
|       1 |       2 |        2 |
|       2 |       1 |        1 |
|       2 |       3 |        3 |

如果要通过查询操作数据，请不要以非数据库固有的格式存储数据.如果您以非本机格式存储数据，则将很难处理它，而且速度会很慢.

don't store data in a format that is not native to the database if you want to manipulate them with queries. If you store data in a non-native format, you'll have a hard time to manipulate it, and it will be slow.

这篇关于寻找一种使用PHP和MySQL来存储表的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！