I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business location, followed by entities for Room, Rack, RackU, and finally device. Now, I know that primary keys are supposed to be unique, but is that unique over the entire database, or unique for the individual branch of the system?

By that I mean, there are one to many rooms in a location, and one to many racks in each room. do the primary keys for the racks in one room in a given location need to be unique from those of either another room in the same location, or even from a room in a different location?

I probably didn't describe this very well, so feel free to ask for clarifications on or at any point.

1 Answer 1


For each relation (ie. table) the primary key value must be unique and immutable (ie. it doesn't change).

You're likely to have a relation for location, one for rooms, one for racks. The primary key values for each of them must all be unique, so two racks in different rooms would not have the same PK value. However you could have the same PK value for a rack and a location, because they are different relations. There would be no significance to the values being the same or different across relations.

  • Hmm, then i should add an attribute to each entity with display name" or "meatspace name" for the label that actually goes on the rack/room/U/etc. It would be easier than renaming/labeling EVERY room/rack/etc with the pk ID#. Thanks!
    – acolyte
    Commented Jun 5, 2013 at 19:13
  • 4
    The unique identifier that you physically label items with should be always different from the PK anyway. Make the PK a simple integer for the internal use of the system only, and then you can label items in the real world according to whatever convention you want to follow, and can change them whenever you like as long as they are unique. Commented Jun 5, 2013 at 19:19
  • 1
    Another option is to have composite primary keys, like (LocationID, RoomID, RackID) with sample value: (1,23,7) (meaning Location:1--Room:23--Rack:7) You'll have many racks with RackID=7 but only one in Room 23 of Location 1. Commented Jun 5, 2013 at 19:34
  • @ypercube i'm trying to be 100% system-agnostic at the moment. Part of what i'm doing is to design a system that would work then find a way to implement it. I don't know if we would be trying to set up a small MySQL server for this, use Sharepoint's (admittedly lackluster DB support), Access, Spiceworks, or anything like that. While I would LIKE to use MySQL, I'm trying to keep things fairly simple. an additional attribute will work just fine.
    – acolyte
    Commented Jun 5, 2013 at 19:51
  • @DavidAldridge alright, that sounds awesome. thanks for the help, :accepted:
    – acolyte
    Commented Jun 5, 2013 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.