Relationships

[neo651]

I'm new to Access and the concept of Normalization.

I manage my company's devices (computers, access points, switches, etc). I'm looking to keep tabs on our static IPs better so I'm creating a table that stores all IPs in a unique column so we don't get duplicates. The problem is that we have 14 different devices that have static IP addresses and these 14 devices are in 14 different tables. Now, this is where it gets tricky, I think, sometimes a device will have multiple IPs and sometimes one IP will belong to multiple devices.

How do I accomplish this?

Thank you.

[alansidman]

I would use two tables. One for the devices:

DeviseID (PK)
DeviseName
OtherInfo
...
...
...

Second Table would hold the IP Addresses

IPAddressID (PK)
DeviseID (FK)
IPAddress

Then in a query, join the two tables on the DeviseID fields.

Alan

[rpeare]

If this is a many to many relationship you may need one table for devices, one table for IP addresses and a junction table that shows the deviceID and the IPID

for instance

tblDevices
DeviceID DeviceName
1        Printer A
2        Printer B
3        Computer A
4        Computer B

tblIP
IPID IPAddress
1    111.222.333.444
2    111.222.333.555

Now let's say that printer A and computer A are both related to IP 111.222.333.444. And PrinterA, PrinterB and Computer B are all related to IP 111.222.333.555

your junction table would look like

tblIPDevice
IPDID IPID DeviceID
1     1    1
2     1    3
3     2    1
4     2    2
5     2    4