Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Jun 5 '13 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. –  David Aldridge Jun 5 '13 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. –  ypercube Jun 5 '13 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 Jun 5 '13 at 19:51
    
@DavidAldridge alright, that sounds awesome. thanks for the help, :accepted: –  acolyte Jun 5 '13 at 19:52
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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