Database-Design
記錄可變數量的列?
我正在嘗試為設備建模電纜映射。每個設備都有多個卡,每個卡都有多個埠。由於卡和埠的數量都不同,我對如何建立正確的規範化形式和關係感到困惑,以解決一個設備記錄具有任意數量的卡和具有任意數量的埠的那些卡。有什麼想法嗎?
這聽起來像是一個非常簡單的一對多關係。
對於 SQL Server,我會這樣寫:
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;
這允許 a
Device
擁有多個Cards
,而後者又可以擁有多個Ports
。結果:
3個表可以
JOINed
像這樣在一起: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;
如果你
LEFT JOIN
這樣使用: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;
你得到這些結果:
這是顯示表關係的圖像: