database design question - How to make the table 3NF and have no orphans I trying to find out what is the best way to build schema for port-nic associations:

I am storing all information about ports (e.g eth0, eth1, lo0, lo2 of a Linux pc) in a table called ports and NIC information in nics (for storing slot info, etc.)

I am trying to find out if I should store a network-device port information all in one table by a column type_of_port to separate types of ports. 

Using type_of_port, the advantages are that: 1. I have only one table, e.i easier queries, 2. I don't have stale orphans if a NIC is removed. Disadvantage: is that I have nic info as NULL values for loopbacks

sqlfiddle: http://sqlfiddle.com/#!9/50977/1:

CREATE TABLE nics (
    nic_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slot_no INTEGER UNSIGNED NOT NULL
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE ports (
    port_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nic_id INTEGER UNSIGNED DEFAULT NULL COMMENT 'references to NIC',
    FOREIGN KEY (nic_id) REFERENCES nics(nic_id) ON DELETE CASCADE,
    serial_no VARCHAR(100) DEFAULT NULL COMMENT 'serialNumber of NIC;',
    port_name VARCHAR(100) UNIQUE NOT NULL COMMENT 'e.g eth0',
    type_of_port VARCHAR(3) NOT NULL COMMENT 'phy, vir'
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

insert into nics(nic_id, slot_no) VALUES(1, 0);
insert into nics(nic_id, slot_no) VALUES(2, 1);
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(1, '0000', 'eth0-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(2, '1111', 'eth1-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo0', 'vir');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo1', 'vir');


OR 

by its type e.i one table for loopbacks, another table for physical ports. 
Disadvantage here is that, ports are scattered over different tables for each type of port. But the advantage is that its in 3NF.

Any suggestions?
The closer to reality you map, the more flexible the design usually is (and that usually means normalize it!).

I wouldn't worry about orphans if you have your F-key constraints set. In the case of deleting a NIC though, remember reality...did we really set the NIC on fire and crush it into oblivion? If so, then in your example, deleting a NIC will delete the port also...which is good. For making what is plugged in appear for normal "active" views, slot_no IS NOT NULL can be a part of the WHERE clause.

For both slot_no and the case of loopback (no NIC), having a NULLable column in InnoDB doesn't hurt anything, maps well to reality, and is made for just such a case. It means "I don't have one" in both columns, which is perfect. 

If it were me, and I thought I might want metadata about the ports (lanes, speed, CPU association), I'd even go as far as having an additional table for slots which are referenced by NICs (some multi-jack cards appear as more than one) by that slot_no column you have.

Off the top of my head, here are some natural views (WHERE clause with "slot_no IS NOT NULL" would work in your 2 table design too):


If you start your queries include an inner join to slot, NICs which are not plugged in (but metadata retained) will not appear for active listings
For viewing metadata about all NICs (past and present), outer join instead
For an auto-configuring script, unplugged NICs or rearranged NICs can retain their metadata and port associations without them showing in active SELECTs (see #1)


In any case, be sure to keep the NIC info with the NICs.