Question 3 way relationship help please (1 Viewer)

Hi all,
I have been tasked with creating a database for our section and am having a little difficulty with planning the relationships between some tables.

We have an internal LAN and I want to create a database to record the configuration. The main things to be recorded are:
IP adresses
Switch Ports
Patching Details

The problem I have is that a computer on the network would record all of the above. It would have an IP address, It would belong to a certain switch port, and it would have a patch number.
A phone would have A patch number but no IP address and wouldnt go into the switch.
A server or UPS for example has a Switch port and an IP address but has no patching detail.

I have played with different designs such as:
sub tables for each of the tables and then a master table where the joins are recorded, but this is not a great solution.
Also I played with having 2 columns in each table recording the relationships if any to the other two, and when the form is updated by the user, a script then makes the same interactions with the other tables, but this gets really complicated when deleting joins etc.

The bonus is that the allocations are fixed. I have
255 IP addresses - FIXED
72 Switch Ports - FIXED
142 Patch Ports - FIXED

If anyone has any suggestions then there is probably a way of doin this that I just havnt thought of but I am really struggling to find the answer lol.

Any help appreciated.

Kind Regards

Keith

I'd say you have to consider what your aim is in terms of what you're trying to achieve, ie what's your intended output or what functionality is required. You say "but this gets really complicated when deleting joins etc." Why is this necessary.
If you have to have a structure where you have devices linking to other objects, then I'd suggest you need tables based on device type so that the joins are stable unless you're writing your queries in VB SQL where you can establish from the device type how tables will join.
You may have to elabourate on what you're trying to achieve here.

David

David,

Thank you for the quick reply. I am trying to make it easy for the other IT admins in the office to display the information in a database by using a form as they are not really database literate.
I can display the information no problem, but if they make a change to the information ie. re-patch a terminal, then they would need to update the table IP addresses to show the new patch number, then the patchdetails table, to show the new IPAddress linked to that patch, then the switch port table to update the patchnumber linked to that port. It gets really confusing for them updating three tables everytime.

I tried to do this automically via a sql script running update queries, but then you also have to run a script to delete the previous data and check for anomylies ie. The same patch panel related to 2 x switch ports etc. HEADACHE lol.

This is why I tried having a different scenario:
stblIPAddresses
stblPatchPanel
stblSwitchPort
and a tblMasterConfig
The master config just contained 3 x columns containing the id field of all three tables. But this presented further problems in duplication, where a IPaddress would have a link to patch panel in one row, then another row would have the patch panel linked to the switch port.

All I am after is an idea of how someone with a similar scenario, 3 integrated tables, would link the data so that it always gives a true reflection of all data with minimal input to the user.

Hope this helps

Keith

interesting problem. Can't you get away with using just one table your tblMasterConfig. If the design were such that it could hold all your device details, including device type (PC, Phone, Server etc), any other attribute details plus IP address, PatchPanel & SwitchPort, obviously some of the last 3 fields will be NULL according to device type.
If I understand you correctly, to use your example, if you re-patch a terminal, you can just run a series of update queries based on this terminal's attributes
ie to update all patch numbers that use this device's IP address
Variables Me.Patchnumber, Me.IPaddress and Me.portNumber are assumed to be string data types and fields on your form for the device you've just edited.
You could have a form that displays all devices, all fields to enable editing of IP address, patch or port number.
You could also display by selected device type or selected device, the recordsource will be a vba Select statement based on the selected device or device type and display for example all devices that connect/use same port/patch .. whatever you choose to display the recordsource is always a vba Select statement using the device type to determine your joins

David

Do you have to record any history? That is, do you need to know the previous configuration or the configuration as of May 15, 2013 for example?