Building a database to show "taken" slots in a server rack

[jeskasper]

Hello all,

I have been twisting my brain with this one, and hope for some help.

What I want to do, is build a view, that will show the layout of a server rack cabinet, and show which slots are taken, and which are available for use.

I have a table called "servers", and a table called "racks". The "servers" table has two fields; "Server_ID", and "Server Name". The "racks" table has got three fields; "Rack_ID", "Server_ID" and "Index_number". They are connected by a relation between the "Server_ID"'s

On the "Servers" layout, I placed a portal to the Racks table, containing the Rack_ID and Index_number fields. When I add a server, I enter which rack it is in and what index numbers it occupies. A rack can contain 35 single units, but a server may occupy more than one unit.

Now, what I want the layout to show, is when I select a specific rack ID - I want it to layout an image of the rack, and put the servers in the right localtions on the rack. For instance, if rack A has a server occupying index 2-4 and another server occupying index 6-10, then I want the layout to show "Free" from 0-1 and from 11-35, and the name of the servers occupying index 2-4 and 6-10.

I hope I have made myself clear and that someone can see what I cannot.

Best regards,

Jes

[comment]

If I understand this correctly, your Racks table is actually a table of Slots? Otherwise the relationship on ServerID doesn't make sense. I would switch the relationship around in any case, so that the position of a server in a rack becomes an attribute of the server, not of the slot.

As for the solution: if a server is 3U high, and is placed in slot #10, then the server can easily calculate the ID of the last slot it occupies (or even the ID's of all the slots it occupies). This can then be used to establish a relationship between the server and all of its slots.

[jeskasper]

Thanks for the reply.

However, the server does not need to know its location. The view should present where the servers are placed.

I have attached some screen shots that show the relationships, tables and the view I am trying to make

[comment]

The way you have it, one server can have many slots. And each of those slots has to be assigned to the server, either manually, or by a script.

If you try to calculate this instead, it will be rather complex, because it will depend on data from both the server (how high is it) and from sibling records (where does it start). And it would be unstored, so you won't be able to tell from the server side which slots it occupies. Much simpler my way, I think.

[jeskasper]

Then I am afraid I need an illustration of the setup of your way. I do not quite understand how you suggest me to build the structure?

[comment]

Something like this?

Racks.fp7.zip

[jeskasper]

This is beautiful - thank you. Now, I only need to make sure that it is not possible to assign a server of 4 units to a spae with only two available units. As it is now, one server, just writes over another if you try to assign a server that is to high. Also, the ID's in the racks should be 1..x on all of them - and now a running number. In the example, the first slot of rack number two will have ID number 17 - it should have ID 1 just as Rack 1. How is that accomplished?

I really appreciate your help. You are most kind.

[comment]

No, the slots definitely need a UNIQUE ID. You can let them have their position in the rack in another field, but you cannot use this field in a relationship. Or you could just display @@ in the portal.

[jeskasper]

Okay, so I should just display the place in the rack graphically, or insert a nother field into the equipment table for the place in the rack?

What do you mean by "displaying @@ in the portal"?

[comment]

See Help > Creating and managing layouts and reports > Drawing and inserting objects on a layout > Inserting the date, page number, or other variable onto a layout.

BTW, you could also let the equipment have the RackID AND the slot number, and have only a limited number of slots that would "change identity" according to the rack selected for viewing. This is difficult to explain, but you can see a demo of this technique here:

http://fmforums.com/forum/showpost.php?post/205259/