Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 9 down vote accepted

This sounds like a really simply 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

share|improve this answer
2  
Clearly, if you wanted to make this "web-scale" you'd need more than just this very simply outline. You might also want primary keys, foreign keys, and indexes, and you may even want to shard these 3 tables across several boxes, with clustering and HADR. –  Max Vernon Mar 27 '13 at 19:14
    
Thanks, this almost makes sense... could you explain the relationships between these tables a little better please? I'm almost there in terms of understanding! –  George K. Mar 27 '13 at 19:24
    
I've added a SELECT statement with a JOIN that shows how this works. I've also added an image that indicates the relationships. –  Max Vernon Mar 27 '13 at 19:32
1  
Thanks a bunch!! This is very very helpful –  George K. Mar 27 '13 at 19:39
    
My pleasure George!. –  Max Vernon Mar 27 '13 at 19:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.