帮助mysql的数据库设计结构

Question

我是数据库和 mysql 的新手，正在尝试了解如何设计数据库。我正在开展测试项目，人们可以在其中查看某些橱柜中的物品。因此，用户可以单击一个柜子并查看其中的所有架子以及每个架子的所有物品。

到目前为止，我已经弄清楚我需要下表：

Item Table  
Item ID, Item Name

Shelf Table 
Shelf ID, Shelf Number

Cabinet Table   
Cabinet ID, Cabinet Name

我所困惑的是我需要什么关系表？我确信这对某人来说真的很容易！

我可以有第四张桌子，里面有类似这样的东西：

ItemID, Shelf ID, Cabinet ID

这有意义吗，这样我就可以查询柜子中的所有架子及其每个架子的物品？我确信这个问题很容易回答，但我的脑子很痛！我被告知如果有帮助的话可以使用 MyIsam 作为存储引擎。

谢谢

编辑：

谢谢，如果您不介意的话，还有一个问题。你知道如何获取所有架子中的所有项目吗，这样它们就会像这样出现在 html 中：

<div id="shelf-1"><p>spoon</p><p>fork</p>
<div id="shelf-2"><p>knife</p></div>

我有以下似乎可行的内容，但通过阅读周围的内容，我认为在查询中循环不是一个好习惯：

$cabinetID = $_GET['cabinetid'];

$sqlShelf = sprintf('   SELECT shelf_id
                            FROM shelf
                            INNER JOIN cabinet ON (cabinet.cabinet_id = shelf.cabinet_id)
                            WHERE cabinet.cabinet_id = %s', $cabinetID);

    $resultShelf = mysql_query($sqlShelf);

while($shelf = mysql_fetch_assoc($resultShelf)) {
                $shelfID = $shelf['shelf_id'];

                $sqlItem = sprintf('SELECT *
                        FROM item
                        INNER JOIN shelf ON (item.shelf_id = shelf.shelf_id)
                        INNER JOIN cabinet ON (cabinet.cabinet_id = shelf.cabinet_id)
                        WHERE cabinet.cabinet_id = %s
                        AND shelf.shelf_id = %s', $cabinetID, $shelfID);

                $resultItem = mysql_query($sqlItem);

                echo('<div>');

                while($item = mysql_fetch_assoc($resultItem)) {
                    echo('<p>' . $item['item_name'] . '</p>' . PHP_EOL);
                }

                echo('</div>');

Answer 1

在写 SQL 之前先用英语思考一下。

你可以有很多柜子；每个柜子有零个或多个搁板；每个架子上可以有零个或多个物品。

一个架子一次只能放在一个柜子里；一件商品一次只能放在一个货架上。

因此，我将其设计为三个表，其中两个表具有一对多关系。

CREATE TABLE IF NOT EXISTS cabinet
(
    cabinet_id bigint not null auto_increment,
    primary key(cabinet_id)
);

CREATE TABLE IF NOT EXISTS shelf
(
    shelf_id bigint not null auto_increment,
    cabinet_id bigint,
    primary key(shelf_id), 
    foreign key(cabinet_id) references cabinet(cabinet_id) on delete cascade on update cascade 
);

CREATE TABLE IF NOT EXISTS item
(
    item_id bigint not null auto_increment,
    shelf_id bigint,
    primary key(item_id), 
    foreign key(shelf_id) references shelf(shelf_id) on delete cascade on update cascade 
);

这是一个示例查询，它将为您提供特定柜子中的所有物品（刚刚尝试过 - 效果很好）：

SELECT item.item_id, cabinet.cabinet_id
FROM item 
INNER JOIN shelf ON (item.shelf_id = shelf.shelf_id)
INNER JOIN cabinet ON (cabinet.cabinet_id = shelf.cabinet_id)

Answer 2

您将需要这三个表之间的两个表来保存与关系相对应的数据。

Shelf_Cabinet Table
ShelfID, CabinetID

和

Item_Shelf Table
ItemID, ShelfID