How to uniquely identify a device.

[nkline]

Greetings,

It's been awhile since I've actually had to design a database schema.
Lets say you're an I.T. person at your organization and you want to development
some type of inventory system that keeps track of all your servers, switches, routers,
environmental monitoring units, KVM's, etc. This inventory system will use a MySQL database
to organize things. Your core question is "How do I uniquely identify each one of my devices?"
That's my question too.

I was thinking this over and came up with several criteria by which a device could be
uniquely identified. A device could be uniquely identified by:

-serial number
-name
-IP address
-MAC address
-some database-generated number

Then I thought about the pro's and con's of each of these items:

-Serial number:
Pro's: Each device has a unique serial number.
Con's: However each serial number can have several devices. For example, virtual hosts(think VMware). One virtual host
can have many virtual servers inside of it. As a result, many devices can have the same serial number.

-Name:
Pro's: Each device has a unique name. No two devices can have the same name.
Con's: ?

-IP Address:
Pro's: Each device has a unique IP address. No two devices can have the same IP address.
Con's: One device can have several IP addresses (some funky NIC setup). A virtual host that uses NAT for its virtual servers (this is probably
rare though). A network device that performs NAT and has computers behind it(again, probably rare at our organization).

-MAC Address:
Pro's: Each device has a unique MAC address but do ALL devices have a MAC address? No two devices can have the same MAC address.
Con's: ?

-Some database-generated number:
Pro's: Each device would have its own unique # that would be generated by MySQL. No two devices can have the same #.
Con's: none.

So my question is: what's a solid "key" to use which can uniquely identify each device? Maybe the best option is using two keys?
Or maybe there's a better key to use besides that which I've mentioned here? All suggestions are greatly appreciated.

Thank you for your time,

*Nick*

[vinyl-junkie]

I haven't been the one to design such a database, but I can tell you that we have database-generated numbers on our PCs, keyboards, etc. These numbers are printed on bar-coded stickers and attached to the device which is identified by them. Our network support people go by these stickers when they do their periodic inventories.

I don't know how they've identified the servers, but my guess is that they are done the same way.

Hope this helps.

[Fumigator]

In the past I've always preferred an auto-generated primary key. There is a rule out there that says if you can make a unique key out of data you are already storing (such as serial number plus a product name making up a compound primary key) then you should use that, but you have to deal with the headache of manually entering the serial number, dealing with incorrectly entered numbers, etc. which in my experience is more trouble than it's worth. It's one thing to have to correct a "dead-end" serial number, it's another thing to have to correct it PLUS all the places you've stored that number as a foreign key in other tables.

In cross-reference tables (look-up tables) I will try to use compound keys (which were all auto-generated) as the primary key.