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