My 1st Project

[SoulAssassin]

Hi guys

I'm a bit of a newbie using MySQL and PHP, and already have a big project to start. This project requires me to set up a database for off site storage facilities. eventually I need to know exactly which site, room, row, rack, level,  layer, column, and position each box is.

Do you guys think I should set the tables so each table hold site_id, room_id, row_id etc.??? Or should I do it with joins?

Any help or suggestions would really be appreciated.

Here are some images to explain a bit better what it will look like.

 

Once a Site is selected, a room need to be selected:

 

Each Room has Rows:

 

Each Row has Racks:

 

Each rack has Levels:

Each Level has Layers, Columns, and Positions (Depth).

 

This is what I planned my DB to look like, but I don't feel to comfortable with it. People need to be able to Add a box into a vacant position, ad search for it.

 

Any suggestions?

Thanks

[obsidian]

Since it appears you are actually simply storing coordinates within the room, I would suggest you simply store the coords in each record, not tying it to anything. In this case, it's sort of like a filing cabinet: I should be able to tell you to look in the 3rd drawer down of the 1st cabinet in the 3rd room on hall #2. At the same time, I may give you identical directions but point you to hall #1 instead. The coordinates of the drawer within the hall are the same, so there's really nothing to tie it back to what hall it's on from that direction.

 

My preference in this sort of 3D grid structure would be to have a column for each of the 6 possible coordinates (row, rack, level,  layer, column, and position) within a room. Then, I would define the sites and rooms in their own tables and simply tie the entries to a room (which in turn is tied back to a site). That way, all you have to do is define the coordinates of the item you are after and tell the query what room it is in rather than having all the joins necessary.

 

Hope this helps some.

[SoulAssassin]

Thank you very much.

I think I was complicating it a bit to much, but your solution makes more sense.

So if understand correctly, it should look like this:

site

site_id

site_name

site_address

 

room

site_id (linked to specific site)

room_id

room_number

 

row

row_id

row_number

 

rack

rack_id

rack_number

 

level

level_id

level_number

 

layer

layer_id

layer_number

 

position

position_id

position_number

 

column

column_id

column_letter

 

I don't need to join, or link any of the tables, except the site and room?

How will I know which positions are still available, and which ones are used if I use this method?

 

Thanks for your input.

[obsidian]

If you are looking at predefining all of your locations, you will have to add the joins; however, assuming we are talking about physical rooms here, the number of racks in a room may change. With this in mind, you could simply make some assumptions on the sizes of the racks, etc, when you run your queries. Here is what I was thinking:

Sites
============
id
name
address


Rooms
============
id
site_id
room_number


Item
============
id
name
room_id
row
rack
level
layer
position
column

 

With this structure, you would be able to query for all used positions (coordinates) in a given room or site, and then based on that, you would know that anything not in that list is still available. If you are looking at predefining every available position and being able to run negative queries against it to find out all available coordinates, you would indeed have to make your full table structure with joins all the way back to the site_id from columns.

 

Hope this makes a little more sense.

[SoulAssassin]

Unless I predefine and insert all possable Items, but the vacant ones with a additional field to say it's still vacant. If a new Item arrives, load it into a vacant slot, and change status to occupied.

That might work.

location

============

item_id

name

room_id

row

rack

level

layer

position

column

box_number (if empty, it's still vacant)

 

Then have a table called box

 

box

======================

box_id

location_id

[bubblegum.anarchy]

Is layer and level the same value and row and position the same value too?

[SoulAssassin]

No, within every row, there are racks.

Within every level, there are 3 layers, 3 columns and 3 positions (3D).

 

[bubblegum.anarchy]

hmm.. I am a little confused, according to the above picture I could identify any box (server) with only three bits of information, the layer, the column and the position... whats the point of the other information (rack and level)?

 

note: a rack can be identified by a column and a position

[SoulAssassin]

See image below which might make more sense.

I have:

site

every site has multiple rooms

every room has multiple rows

every row has multiple racks

every rack has multiple levels

 

then every level (as above) has:

3 layers

3 columns

3 positions

 

[bubblegum.anarchy]

See if you can wrap your head around this instead:

 

DROP TABLE IF EXISTS site;
CREATE TABLE site (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
     label VARCHAR(100) NOT NULL DEFAULT 'Label not defined' COMMENT 'site label',
     address VARCHAR(255) NOT NULL DEFAULT 'Address not defined' COMMENT 'site address',
     PRIMARY KEY (id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS room;
CREATE TABLE room (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
     label VARCHAR(100) NOT NULL DEFAULT 'Label not defined' COMMENT 'room label',
     layer_limit INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'layer limit',
     column_limit INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'layer limit',
     row_limit INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'layer limit',
     PRIMARY KEY (id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS server;
CREATE TABLE server (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
     site_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'site.id foreign key reference',
     room_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'room.id foreign key reference',
     layer_num INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'layer position',
     column_num INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'column position',
     row_num INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'row position',
     PRIMARY KEY (id),
     INDEX (site_id)
) TYPE=MyISAM;

INSERT IGNORE INTO site (label, address) VALUES ('Primary', '101 Brick Road');
INSERT IGNORE INTO room (label, layer_limit, column_limit, row_limit) VALUE ('White Room', 3, 3, 3);

INSERT IGNORE INTO server (site_id, room_id, layer_num, column_num, row_num) VALUE 
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 1, 1),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 1, 2),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 1, 3),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 2, 1),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 2, 2),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 2, 3),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 3, 1),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 3, 2),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 1, 3, 3),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 2, 1, 1),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 2, 1, 2),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 2, 1, 3),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 2, 2, 1),
((SELECT id FROM site WHERE label = 'Primary'), (SELECT id FROM room WHERE label = 'White Room'), 2, 2, 3);

SELECT server.* FROM server 
     INNER JOIN site ON site_id = site.id AND site.label = 'Primary'
     INNER JOIN room ON room_id = room.id AND room.label = 'White Room'
;

SELECT layer_num AS next_available_layer, count(*) FROM server 
     INNER JOIN site ON site_id = site.id AND site.label = 'Primary'
     INNER JOIN room ON room_id = room.id AND room.label = 'White Room'
GROUP BY layer_num
HAVING count(*) < ( SELECT column_limit * row_limit FROM room WHERE label = 'White Room' );

 

That last query returns the next available layer and nothing is returned that the room is full.... try and extrapolate that query to provide the next available column and row... well... this is what I would do if confronted with the same task.

[SoulAssassin]

That's a mouth full.

That was more or less what I was thinking to, but it's to much DB development, which is not my strong point. I will have a look at it, and play around a little.

The thing is, there's much more tables involves.

multiple sites

site <- multiple room (every site has multiple rooms that must link to that specific site)

room <- multiple row (every room has multiple rows that must link to that specific room)

row <- multiple rack (every row has multiple racks that must link to that specific row)

rack <- multiple level (every rack has multiple levels that must link to that specific rack)

 

Every level has 3 layers, 3 columns and 3 positions that must link to that level

level <- 3 layer

level <- 3 column

level <- 3 position

 

Do you think I am over complicating it for myself?

[bubblegum.anarchy]

What has to be done has to be done some way, I would just do what you need done my way