如何将不同的元素关联到一个到多个类别？

I need to make an interface in which you can select each hardware / materiel part of many electronic devices :

我需要创建一个界面,您可以在其中选择许多电子设备的每个硬件/材料部分:

In a table i'm storing all the devices :

在表中我存储所有设备:

 :Name:     :Id:
Computer     1
Laptop       2
Smartphone   3
...         ...

I need to be able to associate different kind of materiel with them, by materiel i mean this table in which different items are stored :

我需要能够将不同类型的物资与它们联系起来,通过物资我的意思是这个表中存储了不同的物品:

 :Name:         :Id:
Keyboard         1
Screen           2
Mouse            3
Motherboard      4
Webcam           5
...             ...

my problem is, for instance, that i need to associate the item Keyboard with both computer AND laptop ...

我的问题是,例如,我需要将键盘与计算机和笔记本电脑相关联......

I already succeeded to associate items one by one, but because some devices got many items in common (like the keyboard) i need to be able to associate one item to all the devices i want.

我已经成功地将项目逐个关联,但由于某些设备有许多共同的项目(如键盘),我需要能够将一个项目与我想要的所有设备相关联。

So my question is simple: How can i store which item is associated with which device in a MySql database ?

所以我的问题很简单:如何在MySql数据库中存储哪个项与哪个设备相关联?

Any hint there ?

有暗示吗?

EDIT :

Okay guys, so it seems that the Many-to-Many relation is the best way to do this, But anybody got some hint on how i should use sql queries to use the third table ? so i need to store for instance

好吧,伙计们,所以似乎多对多关系是最好的方法,但有人提示我应该如何使用SQL查询来使用第三个表?所以我需要存储例如

:id1:        :id2:
  1            2
  1            3
  2            4
  3            1

But is there simple sql commands to do such things or do i need to use heavy php scripting in order to do this ?

但有没有简单的SQL命令来做这样的事情或我需要使用沉重的PHP脚本来做到这一点?

3 个解决方案

#1

If I understand, it's a many to many relation.

如果我理解,这是一个多对多的关系。

So you need a another table for this relation. This table is called a junction table

所以你需要另一个表来表示这种关系。该表称为联结表

This table would look like

这个表看起来像

DeviceID   |     MaterialID

And the primary key will be both columns.

主键将是两列。

So for the same Device, you can have many material and for the same material you can have many devices

因此,对于同一设备,您可以拥有许多材料,对于相同的材料,您可以拥有许多设备

Here's a good article about it

这是一篇关于它的好文章

To insert your data. It's gonna be 3 basic insert into.

插入数据。这将是3个基本插入。

You need to insert in the device and material table first. Then you do the other insert in the junction table, cause you will have foreign key constraints on both field.

您需要先在设备和材料表中插入。然后在联结表中执行另一个插入,因为您将在两个字段上都有外键约束。

To select all devices with a keyboard you would do something like this

要选择带键盘的所有设备,您可以执行以下操作

SELECT device.name FROM device INNER JOIN junctionTableName on device.id = junctionTableName.deviceId WHERE junctionTableName.materialId = yourkeyboardid

You need a JOIN between the 2 tables. Here's an example where you know the keyboardID but if you don't know it, you would have to do a subquery or a query before to select the id where name = keyboard.

您需要在两个表之间进行连接。这是一个你知道keyboardID的例子,但如果你不知道它,你必须先做一个子查询或查询,然后选择name = keyboard的id。

#2

You will need a table to express this M:N relationship.

你需要一张表来表达这种M:N的关系。

Like 'device_material' table, containing colums: device_id and material_id

与'device_material'表一样,包含colums:device_id和material_id

#3

-1

You could use a third column in your second table

您可以在第二个表中使用第三列

id      item        associate
-----------------------------
1       keyboard    laptop
2       keyboard    computer
3       mouse       computer
4       webcam      laptop
5       webcam      computer

and then something like:

然后是这样的:

select * from devices_table d join accessory_table a on a.associate = d.name where foo = 'bar'

select * from devices_table d join accessory_table a on a.associate = d.name where foo ='bar'

#1