Record with variable number of columns?

Question

I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to establish the correct normalized form and relationships that would address one device record having an arbitrary number of cards and those cards having an arbitrary number of ports. Any thoughts?

Answer 1

This sounds like a really simple one-to-many relationship.

For SQL Server, I would write this like:

CREATE TABLE Devices
(
 DeviceID INT
 , DeviceName nvarchar(255)
);
CREATE TABLE Cards
(
 CardID INT
 , CardName nvarchar(255)
 , DeviceID INT
);
CREATE TABLE Ports
(
 PortID INT
 , PortName nvarchar(255)
 , CardID INT
);
INSERT INTO Devices VALUES (1, 'Test Device 1');
INSERT INTO Devices VALUES (2, 'Test Device 2');
INSERT INTO Cards VALUES (1, 'Card 1 in Test Device 1', 1);
INSERT INTO Cards VALUES (2, 'Card 2 in Test Device 1', 1);
INSERT INTO Cards VALUES (3, 'Card 1 in Test Device 2', 2);
INSERT INTO Cards VALUES (4, 'Card 2 in Test Device 2', 2);
INSERT INTO Ports VALUES (1, 'Port in Card 1, Device 1', 1);
INSERT INTO Ports VALUES (2, 'Port in Card 2, Device 2', 4);
SELECT *
FROM Devices;
SELECT *
FROM Cards;
SELECT *
FROM Ports;

This allows a Device to have multiple Cards, which in turn can have multiple Ports.

The results:

enter image description here

The 3 tables can be JOINed together like this:

SELECT DeviceName, CardName, PortName
FROM Devices 
 INNER JOIN Cards ON Devices.DeviceID = Cards.DeviceID
 INNER JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

enter image description here

If you use LEFT JOIN like this:

SELECT DeviceName, CardName, PortName
FROM Devices 
 LEFT JOIN Cards ON Devices.DeviceID = Cards.DeviceID
 LEFT JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

you get these results:

enter image description here

This is an image showing the table relationships:

enter image description here