I'm thinking that there might be a bit of overkill with that many tables, though really I would be using 4 as well (Office, Box, Device, Switch). What I do need is a "mediator" (?) table, which would be the Device Table :

DeviceID Primary Key (autonumber)
DeviceType Track if it is a server, printer, desk, Box, Switch, etc.
DeviceName Name of the device (Server Name, Printer Name, boxNumber, SwitchIP:Port, etc.)
ConnectionType Should denote if this is connected via BoxNumber, or to Switch?
ConnectionID This should either be a boxnumber or SwitchIP:SwitchPort. Should be selectable based on ConnectionType
LocCode Office code

I'm thinking with this extra table (I don't have this yet), I can then carry my connections from, say, a floor printer all the way back to the L3 Switch:

DeviceID : 1
DeviceT : Printer
DeviceName : Xerox12345
ConnectionT : Box
ConnectionID : 324-PD001

  • DeviceID : 2
  • DeviceT : Box
  • DeviceName : 324-PD001
  • ConnectionT : Switch
  • ConnectionID : 111.111.333.010:18
    • DviceID : 3
    • DeviceT : Switch
    • DeviceName: 111.111.333.010:50
    • ConnectionT : Box
    • ConnectionID : BB-010
      • DeviceID : 4
      • DeviceT : Box
      • DeviceName : BB-010
      • ConnectionT : L3Switch
      • ConnectionID : 111.111.444.254:A3


For the forms, the DeviceName can be a lookup (really only for Box and Switch, to make sure that the name is entered correctly) based on the DeviceType. I'm sure I can generate a lookup table for the switches based on the SwitchPorts (numeric field) so that it generates the correct port numbers. Same with the ConnectionIDs. I still seem to be missing something, to keep the data "clean". I have a DeviceID, but what stops me from setting 2 different Box numbers to the same switch port? I was thinking that the DeviceID could be the start point, but that may lead to duplication across offices that might have similar layouts.... I feel that I am close to what I need, but would like to remove the possibility of incorrect data entry....