I'm designing a system that includes the following.

Computer --< MAC Address --< IP address --< DNS name

Note: This is only for tracking fixed IP addresses, not DHCP. A network ops center reserves/assigns fixed IP addresses. Usually a DNS name is paired with an IP address, but sometimes IP addresses are handed out without a DNS name (I don't see that as a problem at all here, I'm just mentioning it).

Computer records must be unique in the set of records.

MAC addresses must be unique in the set of records.

The design seems pretty simple at first, but there is an issue I'm looking for feedback on.

IP addresses (and usually with a DNS pairing) exist in the system…

1) … and are related to a computer, but without a real MAC address (the MAC address hasn't been found/entered).

or

2) … are related to neither a real MAC address nor a real computer (they're simply available for future assignment).

If I broke that down, I think it would look like:

Computer --< MAC Address --< IP address --< DNS name

Computer --< IP address --< DNS name

IP address --< DNS name

However to avoid creating the latter two relationship chains, I'm considering having records with values of "UNASSIGNED_001", "UNNASSIGNED_002", etc., as needed, for MAC address records, and a single "UNNASSIGNED" Computer record -- so that I could create a relationship between an IP address and a MAC address (actual IP to "UNASSIGNED_001" MAC address), and then "UNASSIGNED_001" Mac address to "UNASSIGNED" Computer, OR from an IP address to a computer record of an actual computer.

Note that in both sets of examples following, the "UNASSIGNED" computer records represent the same record.

Legitimate examples:

Computer_001 -- 00-B0-D0-86-BB-F7 -- 19.22.23.1 -- orange.fake.com

Computer_002 -- UNNASSIGNED_001 -- 19.22.23.2 -- grape.fake.com

UNNASSIGNED -- UNNASSIGNED_002 -- 19.22.23.3 -- cherry.fake.com

UNNASSIGNED -- UNNASSIGNED_003 -- 19.22.23.4 -- lettuce.fake.com

Examples that would not work:

Computer_003 -- UNNASSIGNED -- 19.22.23.5 -- carrot.fake.com

Computer_004 -- UNNASSIGNED -- 19.22.23.6 -- turnip.fake.com

UNNASSIGNED -- UNNASSIGNED -- 19.22.23.7-- rutabaga.fake.com

I could not simply use a single "UNNASSIGNED" record in MAC address, since in actual use, I'd need that to be related to one and only one computer record, but that value could need to be related to multiple real computers, as well as to the one "UNNASSIGNED" computer record -- which the relationship I've outlined would not support.

Using a join table might work, but if I can keep the relationships as originally outlined, I think I'd prefer that.

One other consideration with using "UNNASSIGNED…" is that if I use any kind of data validation (i.e., for MAC address entries), I think I'd have to create a calc that would allow "UNNASSIGNED…" as a valid entry.

I am not a big fan of dummy records . I think you could solve this through:

Computer --< MAC Address --< IP address >- Computer 2

Computer --< IP address 2 --< DNS name

where the relationship Computer --< IP address 2 is based on calculation fields combining both ComputerID and MACAddressID - so both types of IP address are included.

That looks very interesting; I'll give it a try. Thanks!