Help with database design structure for mysql

Question

I'm new to databases and mysql and am trying to get my head round how to design my database. I'm working on test project where people can view what items they have in some cabinets. So a user can click on a cabinet and see all the shelves inside it and all the items for each shelf.

So far I've worked out I'll need to following tables:

Item Table  
Item ID, Item Name

Shelf Table 
Shelf ID, Shelf Number

Cabinet Table   
Cabinet ID, Cabinet Name

The bit I'm stuck on is what relational tables I'd need? I'm sure this is really easy for somebody!

Could I have a fourth table which holds something like:

ItemID, Shelf ID, Cabinet ID

Would that make sense so I could query all shelves in a cabinet and their items for each shelf? I'm sure this is very easy to answer but my brain hurts! I've been told to use the MyIsam as the storage engine if it helps.

Thanks

Edit:

Thanks, another question if you don't mind. Do you know how to get all the items in all the shelves, so they appear in the html like this:

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

I have the following which seems to work but from reading around I don't think it is good practice to loop inside a query:

$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>');

duffymo · Accepted Answer · 2011-05-21 22:36:49Z

2

Think about it in terms of English before you write SQL.

You can have many cabinets; each cabinet has zero or more shelves; each shelf can have zero or more items on it.

A shelf can only be in one cabinet at a time; an item can only be on one shelf at a time.

So I'd design this as three tables, with one-to-many relationships for two of them.

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 
);

Here's a sample query that will get you all the items in a particular cabinet (just tried - works great):

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)

edited May 21, 2011 at 22:36

answered May 21, 2011 at 17:42

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Over a year ago

Thanks that's great, helps to think about it like that. Can I be really cheeky and get an example query!

Bob Over a year ago

It won't let me, I don't have enough rep :(

Naveed Butt · Accepted Answer · 2011-05-21 17:47:41Z

0

You would need two tables between those three tables to hold the data corresponding the relations.

Shelf_Cabinet Table
ShelfID, CabinetID

and

Item_Shelf Table
ItemID, ShelfID

answered May 21, 2011 at 17:47

Naveed Butt

2,9177 gold badges33 silver badges58 bronze badges

1 Comment

duffymo Over a year ago

This is true if there are many-to-many relationships between shelf and cabinet, item and shelf, but I don't believe that physics will allow it.

Collectives™ on Stack Overflow

Help with database design structure for mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related