Linking several tables to one table

[mkj]

Hi,

I am creating a database that stores IP addresses for various devices
(network switches, computers etc) on the network. I had hoped to be able to
have one table in the middle that stores an IP address to a serial number.
Seperate tables, one for computers and one for switches would link to this
table to get the IP address for the device's serial number.
Unfortunatly, after I've created the relationships, when I add a new
computer to one table and it's ip address to the other, a message appears
saying that I need a matching serial number in the switches table.

I can only get around this by turning off referential integrity on the
relationships, but that feels wrong. Is there another way?

The tables in my test are:
Table: T_IPAddresses,
Fields: ID{PK}, SerialNo, IPAddress
Table: T_Computers,
Fields: SerialNo{PK}, PCName
Table: T_Switches,
Fields: SerialNo{PK}, NumberOfPorts

There is a 1-many relationship between T_Computers(SerialNo) and
T_IPAddresses(SerialNo), and another 1-many relationship between
T_Switches(SerialNo) and T_IPAddresses(SerialNo).

Cheers,

 

[Jeff Boyce]

Another way of looking at your situation (as I understand it) is that you
have devices. Devices have types (switch, computer, ...). Devices have
serial numbers. Devices have IP addresses. All those facts can be stored
in your [Devices] table.

Each type of device has its own characteristics, so you need separate tables
to help describe each type (i.e., switch, computer, ...). These are related
1:1 to the [Devices] table -- that is, one row in the [Devices] table has
one (and only one) entry in (at most) one of the other tables ([Switches],
[Computers],...).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[mkj]

Thanks for the swift response.

I've had a stab at creating a test database along the lines you have
suggested. Unfortunately, in the IP addresses table, I can't set the serial
number as the primary key to get the 1-1 relationship, because the same
device could have two IP addresses (we have servers that sit on two networks).

Any other suggestions would be appreciated.

Cheers,

Mike

Another way of looking at your situation (as I understand it) is that you
have devices. Devices have types (switch, computer, ...). Devices have
serial numbers. Devices have IP addresses. All those facts can be stored
in your [Devices] table.

Each type of device has its own characteristics, so you need separate tables
to help describe each type (i.e., switch, computer, ...). These are related
1:1 to the [Devices] table -- that is, one row in the [Devices] table has
one (and only one) entry in (at most) one of the other tables ([Switches],
[Computers],...).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I am creating a database that stores IP addresses for various devices
(network switches, computers etc) on the network. I had hoped to be able to
have one table in the middle that stores an IP address to a serial number.
Seperate tables, one for computers and one for switches would link to this
table to get the IP address for the device's serial number.
Unfortunatly, after I've created the relationships, when I add a new
computer to one table and it's ip address to the other, a message appears
saying that I need a matching serial number in the switches table.

I can only get around this by turning off referential integrity on the
relationships, but that feels wrong. Is there another way?

The tables in my test are:
Table: T_IPAddresses,
Fields: ID{PK}, SerialNo, IPAddress
Table: T_Computers,
Fields: SerialNo{PK}, PCName
Table: T_Switches,
Fields: SerialNo{PK}, NumberOfPorts

There is a 1-many relationship between T_Computers(SerialNo) and
T_IPAddresses(SerialNo), and another 1-many relationship between
T_Switches(SerialNo) and T_IPAddresses(SerialNo).

Cheers,

 

[Jeff Boyce]

I didn't catch that from your original post...

One device can have multiple IP addresses -- this is a 1:m relationship.
Your tables might look like:

tblDevice
DeviceID
Manufacturer
SerialNumber (NOTE - DON'T use Serial Number as a unique ID!)
...

trelDeviceIP
DeviceIPID
DeviceID
IP

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks for the swift response.

I've had a stab at creating a test database along the lines you have
suggested. Unfortunately, in the IP addresses table, I can't set the serial
number as the primary key to get the 1-1 relationship, because the same
device could have two IP addresses (we have servers that sit on two networks).

Any other suggestions would be appreciated.

Cheers,

Mike

Another way of looking at your situation (as I understand it) is that you
have devices. Devices have types (switch, computer, ...). Devices have
serial numbers. Devices have IP addresses. All those facts can be stored
in your [Devices] table.

Each type of device has its own characteristics, so you need separate tables
to help describe each type (i.e., switch, computer, ...). These are related
1:1 to the [Devices] table -- that is, one row in the [Devices] table has
one (and only one) entry in (at most) one of the other tables ([Switches],
[Computers],...).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I am creating a database that stores IP addresses for various devices
(network switches, computers etc) on the network. I had hoped to be

able
to

have one table in the middle that stores an IP address to a serial number.
Seperate tables, one for computers and one for switches would link to this
table to get the IP address for the device's serial number.
Unfortunatly, after I've created the relationships, when I add a new
computer to one table and it's ip address to the other, a message appears
saying that I need a matching serial number in the switches table.

I can only get around this by turning off referential integrity on the
relationships, but that feels wrong. Is there another way?

The tables in my test are:
Table: T_IPAddresses,
Fields: ID{PK}, SerialNo, IPAddress
Table: T_Computers,
Fields: SerialNo{PK}, PCName
Table: T_Switches,
Fields: SerialNo{PK}, NumberOfPorts

There is a 1-many relationship between T_Computers(SerialNo) and
T_IPAddresses(SerialNo), and another 1-many relationship between
T_Switches(SerialNo) and T_IPAddresses(SerialNo).

Cheers,