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.

Is it possible to have access create primary keys for items by appending a string to the primary key one level above it?

I am trying to create an inventory management database for my company's server ops team, to keep track of things such as switches, servers, UPS's, etc. I have things organized to separate our different locations, the different rooms in said locations, the different racks in said rooms, and finally a 1-1 relationship between an occupied rack U and a device. The primary key for locations would be a simple, 3-letter designation that we use as a site code. The primary key for a room would be that site code plus "-###" where "###" is the displayed room number. This is to simplify the attributes for the room/lower levels (i originally had an auto-gen number for pKey, with a separate attribute for display number). this would continue down, until the rackU and/or device entities. For them, i'm looking to allow the admins to use a form, select the correct Location, Room, and Rack#, then enter a rackU and serial number/asset tag for the device. Then the rackU primary key would need to generate itself to be the primary key value of its associated rack, plus the U number.

To simplify, I would be manually inputting each entry down to rackU, and primary keys would look like:

[Loc] = ABC
[Room] = [Loc]-###
[Rack] = [Room]-A/1 (some racks are lettered, some are numbered)

Then, when entering a new device, user would select Location, Room, Rack, and input a rackU number. I need some way to auto-generate the rackU primary key to be the linked Rack primary key plus the given U number.

It's one of those things where I'm fairly certain I could make it work if I was writing this database from scratch in python or PHP, or if i was using an actual mySQL database, but I'm completely lost as to how to handle this in Access.

share|improve this question
1  
Please (please) follow the advice of the highly upvoted comment on this answer to your previous question. –  Jon Seigel Jun 18 '13 at 20:00
    
@JonSeigel that's all well and good, and I'd love to do so, but in that case, how does a new device entry get placed in the correct spot? Would they need to enter the primary key for the corresponding location/room/rack into different places? or is there some really elegant way to utilize forms to get everything to fall into place? requiring users to know the pKey for the specific rackU they're inserting a device into is insane. –  acolyte Jun 18 '13 at 20:22
1  
@acolyte: Indeed, no human being except maybe the DBA should know an object's PK - it's there for the database, not for human beings. Yes, you'll want a form which allows people to select location, room, and rack, or whatever else makes up your hierarchy, using dropdown lists with the keys hidden. Regarding generating PKs for your racks, what is the problem with using an ordinary integer autonumber field? –  Jon of All Trades Jun 18 '13 at 20:31
    
Incidentally, if we seem a little, uh, "bristly" here, it's because (pardon me for speaking on your behalf, Mr. Seigel), we've seen similar designs before. And had to fix them. We don't want you to go for a design which has known serious problems. –  Jon of All Trades Jun 18 '13 at 20:33
    
@JonofAllTrades the problem is that I need some way to be able to correctly associate devices with rackU, and then rackU with proper rack within room/location. Because multiple rooms can have racks enumerating from the same number (i.e. each room might have a rack "A" or "1"), i can't use a displayname like that to correctly associate rackU with a given rack. Same problem exists for device and rackU, and to a lesser extent, room and location. –  acolyte Jun 18 '13 at 20:39

1 Answer 1

I think the difficultly lies in using the parent table as part of the sub-table's primary key. So long as you have a reference to the parent table, you can always generate a reference that combines Location+Room or Room+Rack. Otherwise the primary key will get too hard to update and you have bad primary key.

I can see two ways to tackle this depending on how many locations, rooms, racks you have. It depends on quantity for your interface.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

  • Have three drop downs on the form.
  • User chooses a location, you then select all the rooms of that location in the next combobox.
  • User then selects the room, you then fill the next combobox with the racks in the room.
  • User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

  • If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
  • If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

  • Add rackSize to tblRack: the size in U's
  • Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.

share|improve this answer
    
o.O this...this is amazing! It hasn't fully solved everything, but it's gotten me thinking. I think i can make the subsequent form fields show results from a query to pull only the rooms associated with the given location, and then the next to show only the racks in that room. then rackU would be entered manually (since i'm only tracking occupied rackU's), as well as the information for the device itself. now i just need to figure out how to...do all that. under the "Rack" entity, i have a "capacity" attrib, but "size" is a good addition to "Device" (a SOP would handle top/bot management) –  acolyte Jun 18 '13 at 20:52

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.