寻找使用PHP和MySQL存储表的更好方法[关闭]

Question

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:

Table example

Item |Part |Quantity

Item1 |123 |2

Item1 |124 |2

Item2 |123 |1

Item2 |125 |3

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.

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.

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:

Item 1 uses the following parts

Part 123 : Quantity 2

Part 124 : Quantity 2

Item 2 uses the following parts

Part 123: Quantity 1

Part 125: Quantity 3

Any help would be greatly appreciated.

Answer 1

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

So you need 3 tables (item, part, and 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 |

Edit: 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.