Managing Port information on 2 tables

[InLaNoche]

I have 2 tables, and I guess I am looking for advice on the best way to store and manage the data. I have a table with the list of all desk/office/room network ports. my other table is a list of the Network switches. I would prefer not to have a set number of fields for the switch table (1-50?) for each port. For now what I have done is stored the switch name and the port name in the first table (a field for each). When I get reports I can do a query to find the switch name and show those network jacks based on the info in that table.

Where things go down hill is when we have switches plugged to switches. And in these cases, the direction of these connections should probably be defined. Would it be better to create a text field and use a delimiter for storing the port info on the switch table? Does anyone have a better idea?

[jonh]

Not sure I understand, but if it's tree/hierarchy structure you want you normally create a third table linking id's as normal with a 'parent' field to link rows.

e.g.

t1
id name
01 xxx
02 yyy


t2
id name
01 a
02 b
03 c
04 d
05 e


t3
t1 t2 parent
01 01
01 05 01
01 03 01
01 02 05
01 04 02


So it not only gives the direction but it can have as much depth as you like. First row has no parent so is the root.

xxx
>...a (root)
>...>...b
>...>...>...d
>...e
>...c

[InLaNoche]

Thanks for the info on this. I probably did not explain it well... the tables (BoxNo and SwitchList) is set up as such:
BoxNo
ID -number
BoxNumber - text
OfficeNumber - text
SwitchIP -text
SwitchPort -number

SwitchList
SwitchIP - text
Ports = number

The number of ports on a switch vary from 24 to 50+, and I did not want to make 64 fields that may never be used. So instead I stored the Port info as shown on the box numbers. Well, some switches will connect to other switches, and so that info cannot be stored in such a way. I was thinking that I could use a text array (csv) to store the port info and parse it later when needed (thus storing it with the switches instead of the boxnumbers).

Would this be the best idea? Or should I bite the bullet and just create the fields?

[jonh]

How are going to display the data?

[InLaNoche]

Right now (I have another post here on the form) I want to use a form to edit and view the data, and possibly create a report grouped by the SwitchIP.... The more further I go, the more I think I need to create the 64 fields....

[jonh]

Well I don't know anything about networks so I can't help from that perspective.


The example I gave above would be good to use with a treeview control where you can edit data and move it around with drag and drop.
There's plenty of info online on how to set up a treeview.


Sometimes it helps to work out data structure if you put a sample into a spreadsheet. Anything that's duplicated should have its own table, etc.

[InLaNoche]

I will look into the treeview. I'm not sure I am following, but a point in the right direction is always helpful. I thought it would be easier to store the port info on the boxnumber side. in this case, there is only the switchIP and the port (2 more fields). When I wanted to find them, I just did a query of the boxnumbers based on the switchIP, and I got my list. But some switches 'host' for others, and they would not exist in the boxnumber table. I would need to be able to define where they plug into, and in that case, I would more need the "connection" (be in a boxnumber or a port on another switch) to be stored with the switch... I know what I am kind of looking for, and it still sounds confusing to me...

Unfortunately, I have taught myself all I know about access, vba, SQL and visio, basically through trial and error, and hammering through pet projects. I am pleased with where I am, but doubt it was the most efficient way to do it... There are a lot of posts on books to read, but I would prefer a teacher/tutor to sound of ideas on. Books rarely talk back....
(ramble ramble)

Thanks again John.

[jonh]

Ok, np.


As I said I know nothing about networks. Hardware really doesn't interest me at all.
So this may be completely wrong, I don't know...


A box (server?) contains (a variable number of ?) switches, switches have a variable number of ports.


pk = primary key
fk = foreign key - primary key from another table


Box
BoxID (pk)
other fields that are only related to boxes
...


Switch
SwitchID (pk)
SwitchIP
SwitchPorts (number of ports this switch has)
other fields only relating to the switch


Port
PortID (pk)
PortIP
other fields related a port




So you have 3 tables that contain data specific to each particular item.


Now you need to join them.


Boxes contain switches, join them up ...


BoxSwitch
BSID (pk)
BoxID (fk)
SwitchID (fk)


Switches contain ports ...


SwicthPort
SPID (pk)
BSID (fk (joined to BoxSwitch))
PortID (fk)


So to find which box a port belongs to, you need to find out which switch the port belongs to first.


In either case there will be X number of port records per switch. You can easily check if any port info is missing by comparing the count of records to the number of ports recorded in the switch table.


Or, instead of BoxSwitch and SwicthPort you could do


BoxSwitchPort
BSPID (pk)
BoxID (fk)
SwicthID (fk)
PortID (fk)


Which would probably make your queries easier to write, but will contain slightly more duplication.

[HiTechCoach]

Thanks for the info on this. I probably did not explain it well... the tables (BoxNo and SwitchList) is set up as such:
BoxNo
ID -number
BoxNumber - text
OfficeNumber - text
SwitchIP -text
SwitchPort -number

SwitchList
SwitchIP - text
Ports = number

The number of ports on a switch vary from 24 to 50+, and I did not want to make 64 fields that may never be used. So instead I stored the Port info as shown on the box numbers.

You have the correct idea. Each port assignment should be a separate record. This way it is dynamic. It can handle any number of porst.

I am unclear what some of the fields are for in the table BoxNo.




Well, some switches will connect to other switches, and so that info cannot be stored in such a way. I was thinking that I could use a text array (csv) to store the port info and parse it later when needed (thus storing it with the switches instead of the boxnumbers).


If design properly you should be able to store switches connect to other switches in the same design just like any other device.

What are boxnumbers?


TIP: Always use use a auto number (or something system assigned) primary key. This will also be use for all relationships. In a well design database the primary key is system assigned and never will change.



IP numbers should never be used as the primary key, especially for a device. IP address can change for a device. And even get reassigned.



Or should I bite the bullet and just create the fields?
NO. Definitely not!


Before you start building any forms, reports, etc. or thinking about data entry, the table design needs to be completed. I highly urge you to get the design properly normalized. Properly design tables is the most critical part to the success of your database.


After you have been doing this awhile you will learn that it is a costly mistake is to start building forms to quickly. When you learn that the table design is not correct you should scrap all the forms. It is hard to give up on all that work. What happens is you keep the poor table design. Now you have to always do a lot of extra work to workaround the poor table design. In the long run it really does save time to start over with the improved table design. If you wait to get the tables properly design then you do not have to wasted time re engineering or always have the extra time it takes to create workarounds.

[InLaNoche]

Thanks again for staying with this. There is no immediate deadline, so I would prefer to flesh this out properly. If you don't mind I'll give more of a breakdown.

Boxes are more the network drops, hence the officenumber field. in the end I would like to be able to pick a box number, and follow it back to where it hits the Layer3 switch (main switch). All the ports on the Layer3 Switch are connected to either servers or other switches, so here's here the breakdown of having the port and switch info only stored in the BoxNumber table.

to give an example, lets say the boxnumber at my desk (office number 4-028) is 415-PD075, and that this box number connects to port 2 on switchIP 192.168.1.100. port 1 on switchIP 192.168.1.100 connects to port A5 on the layer3 switchIP 192.168.1.10. This is as short a jump as possible that I would need to follow.

So in a pinch, I would need to know what switches and ports boxnumber 415-PD075 (or really all the boxnumbers in office 4-028) connect back to, in the even a network trace is needed.

Sorry, one other short jump would be server -> switchIP, so another reason the Port info needs to be with the Switches....

Reporting is half the battle. If it's difficult to enter the data, it's harder to sell this system. So the form for entering the data should generally be based on the switch selected. The 'inputter' should be able to select a switch and then see the ports it has (24, 48, 50, 52). I would prefer that it only shows a number of fields that equal the number of ports (I'm good with VBA and doing that, but more need the help with the DB architecture...) hopefully that gives enough background.

Good points on getting the table design down first, though that ship has sailed. This is more than just a database as it interfaces with a floorplan visio diagram, and soo to also interface with switch/server rack diagrams. Ok, partly sailed, but I am willing to rebuild if needed. Really, the data is fed from a query, and as long as I have the right fields, I can re-design the tables and the SQL queries, I have had to do that once before.

[HiTechCoach]

In my younger days I spent a few years designing and installed networks. Pulled cables, built patch panels, and cleaned up many piles of "spaghetti" wire behind servers.

I have also created several IT support databases that track all the devices on a network. One client has almost 1000 devices.


Here is how I view the model of a network:

1) Devices - which include computers (serves and PCs) , printers, fazes, switches, phones, etc

2) Device Port(s) - a device has network port(s). Some devices have multiple ports. Severs can have multiple NICs. Switch have many ports.

3) Drop - - cable/wireless connection - connects a port on a device to another port on another device.

Device A >> Device A Port 1 -------->> Drop X <<-------- Device B Port 1 << Device B

That is it.

It sounds like at this time you will only include both ends of a drop for a switch to a switch. Using this model you can show all devices if the future need arises.

[HiTechCoach]

Reporting is half the battle. If it's difficult to enter the data, it's harder to sell this system. So the form for entering the data should generally be based on the switch selected. The 'inputter' should be able to select a switch and then see the ports it has (24, 48, 50, 52). I would prefer that it only shows a number of fields that equal the number of ports (I'm good with VBA and doing that, but more need the help with the DB architecture...) hopefully that gives enough background.



What every software project needs:

1) A skilled of a database designer to properly model the information into a normalized structure.

2) A skilled of a UX professional to design the user interface to model the workflow and business processes.

3) A skilled program to build the User Interface based on the designs of the other two.

Having a team of three people, one skilled in each area, sure makes software development easier.




Good points on getting the table design down first, though that ship has sailed. This is more than just a database as it interfaces with a floorplan visio diagram, and soo to also interface with switch/server rack diagrams. Ok,

The needs of other system/application should not any influence with your table design. You will build some "interface" that will pass the data to the other system/application in a format they can consume. Fo example: create an SQL Server view that presents the information in a vay the meets the needs of the Visio diagram.

[SamT]

Use multiple small tables

Rooms Table


Room_ID
Room_Type
Station_IDs













Stations Table


Station_ID
Station_Type
Device_IDs












Station_type can be a desk, Remote Printer, Server, etc


Devices Table


Device_ID
Device_Type
Device_IP













Connections Table


Device_ID
Port_ID
Connector_ID
EndConnector_ID
EndPort_ID
EndDevice_ID


















Every connection is duplicated in reverse. Ie. Every Device+Port has a beginning and an end.

[InLaNoche]

@SamT

I like this idea, though I will massage it to match what I am tracking. This gives more room to customize and shape, though it means almost twice the data to enter... I'm sure I can make the forms populate more than one table at a time, so may only be a bit of a pain for the legacy stuff...

Thanks!

[HiTechCoach]

I agree with SamT's tables for Devices and Connections.

It is how I woulf mode my example of:

Device A >> Device A Port 1 -------->> Drop X <<-------- Device B Port 1 << Device Bas tales it would be:

[Devices Table] (1) --> (many) [Connections Table] (Many) < --- (1) [Devices Table]


@SamT



Connections Table


Device_ID
Port_ID
Connector_ID
EndConnector_ID
EndPort_ID
EndDevice_ID


















Every connection is duplicated in reverse. Ie. Every Device+Port has a beginning and an end.

When you say "Every connection is duplicated in reverse" you do not mean create two record do you? There really is is no need to enter it in reverse in a second record and really should be avoid. It can easily be handled with SQL.

[InLaNoche]

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....

[HiTechCoach]

That might work.

I would test it before creating any forms.

Start by entering all the data for two linked switches and all the devices connected to them. Enter it directly into the tables. A good set of test data will have at least two records in every table. Now see if you can create some queries and reports to present the data as needed. If you can get the data out the way you need it then it is time to create forms.


.... I feel that I am close to what I need, but would like to remove the possibility of incorrect data entry....
It is very difficult to prevent all possible data entry mistakes. A good database design/scheme also helps recover from data entry mistakes. A key to this would be never using data, for example IP address, as a primary key and/or foreign keys. Always use a system generated primary key. In Access use the autor data type.

[InLaNoche]

Ok, so made some test data in a separate DB. I see now that the work will be heavy in the Queries... Because of the range of possibilities for the Connections (Box, Switch, or other devices) it's hard to separate out the Port Number when it comes to Switches, or Module ID and Port Number with L3 Switches. I could have these fields created, and left Null for other types, but would it be fine to keep that info in the ID string (such as 111.111.111.111:[A]:12, for Module A, Port 12)? I'm ok with parsing code and will force formatting on the required devices, but which would be considered better?

Right now, for the most part, I can query what I need, but the above will determine the methods put into practice.

Thanks again for the assistance and guidance with this... I really need to take a coarse on this stuff....

[SamT]

There really is is no need to enter it in reverse in a second record and really should be avoid. It can easily be handled with SQL.
I'm not familiar enoughh with SQL to realize that. I leave it to you to teach the OP the correct methods.

[HiTechCoach]

It really is quit easy. IO user two query. One has the fields reversed to simulate the double enter for the other direct. Then I use a Union query to combine them. The result is a recordset that has each connection twice.! One each direction. I use this a lot to relate peoples together.

[HiTechCoach]

I'm ok with parsing code ....

In my experience with 1000+ database, the need to do that type of workaround is due to to a less that optimal database design. I have never had to do that with a properly normalized database.

[InLaNoche]

Well, I have experience with 3+ database, all self taught, unfortunately.... Which is why I have been frequenting here. I really do appreciate all the feed back and assistance. It will not be a problem for me to create the fields for PortNumber and ModuleNumber, I was just curious if that would be a waste/inefficient/<insert other here>. I will do so and continue the testing. Right now the staff management part of the Database works great, this part is for my team.

[HiTechCoach]

It will not be a problem for me to create the fields for PortNumber and ModuleNumber, I was just curious if that would be a waste/inefficient/<insert other here>. I will do so and continue the testing.

One of the major things following the rules of database normalization does is educes data duplication. by having data entered in only a single place. It also dramatically reduces the number of fields that are a EMPTY.

This ha help: http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf
and also: http://www.accessmvp.com/strive4peace/

[jonh]

There is only so much you can do with table design.
At some point you realise that table structure alone isn't enough.


Everything I've read in this thread points to you wanting a tree structure.
The structure you show in post #16 is a tree...
...and the best way of handling a tree is a treeview control.

Obviously a tv control isn't good for reporting... so you need to decide what kind of reporting you need...

Your output kinda has a relevance to the input.

[InLaNoche]

the report will be a bit simpler that other views of the data. Really, the reports would be by Switch, which I can easily pull from this (working from End points only). What I would need is a lookup feature, which would display the 'tree view'. I think I am also able to handle that, now that I have a better handle on storing the data. You all have been a tremendous help with this. So far I am able to store the data in a manageable way, without having 60+ fields that may never be used (cut that down to 3 fields that will be used for the switches, but I'm good with that).

I wish I could add more to your reputations, but they only let me pass out so much at a time (several of the posts here have been a great help).

Thanks!