Situation 1:
Your tables have one relationship and not two. (example: a Device
belongs to a DeviceType
)
So, keep only one relationship, the one with the composite keys (that include the Primary Key). The other relationship is redundant when the composite one is defined.
I would also suggest you have same names for related columns:
DeviceCategory Table
CategoryCode | Name | Description
----------------------------------------
WKS | Workstation | Description of what classifies an item as a workstation...
LPT | Laptop | Description of what classifies an item as a laptop...
DeviceType Table
DeviceTypeID | CategoryCode | Manufacturer | Model | IsTrackedInOtherSystemDefault
-----------------------------------------------------------------------------------
1 | WKS | Dell | GX1000 | true
2 | LPT | HP | dv4000 | false
3 | WKS | HP | xx9000 | false
Device Table
DeviceID | SerialNumber | DeviceTypeID | IsTrackedInOtherSystem | CategoryCode
------------------------------------------------------------------------------
1 | I81U812 | 1 | true | WKS
2 | N0S4A2 | 1 | false | WKS
3 | 3BL1NDMIC3 | 2 | false | LPT
So, the design would be:
DeviceCategory
--------------
CategoryCode PK
Name U1
Description
DeviceType
----------
DeviceTypeID PK U1
CategoryCode FK U1
Manufacturer U2
Model U2
IsTrackedInOtherSystemDefault
Device
------
DeviceID PK U1
SerialNumber U2
DeviceTypeID FK1
IsTrackedInOtherSystem
CategoryCode FK1 U1
and for the Computer
:
Computer
--------
DeviceID PK FK1
Hostname U1
IPAddress U2
CategoryCode FK1 CHK
The "additional" UNIQUE
keys (the two composite U1
ones) will be needed in most DBMS to enforce the foreign key constraints. I guess this answers your question 2, relationships needs indices to be enforced, so (you have to) use them. They will be used by the DBMS not only to enforce integrity but in your queries/statements, when you will be joining the tables.
The only one that is not needed is the U3
you had in the Computer
table.
About question 3 (the over-engineering part): No, I don't think so but that's just my opinion. And you haven't told us if this is a homework/exercise or a real project, whether you will be holding only your family's or a multi-million company's inventory, etc.
Situation 2
I think what you have is fine and there is no need (and not a good idea) to have referential integrity constraints on these columns. This is a default value that is copied in the second table via a stored procedure (I guess during Inserts on the second table?) or altered by a user. If you add an FK, won't that deny users the ability to override the default?
The names of the two columns are self-explanatory enough for a DBA to understand the functionality.
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:
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;
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:
This is an image showing the table relationships:
Best Answer
I think it is a sound design, based on what is called an associative entity. To query: