General question on structure/method

[Hank.School]

Hello,

One of the functions my database needs is to inventory equipment in relay racks (a couple of thousand). The way I had in mind to implement this is to have a table of racks that users enter with the size in rack units (with other information) and a separate table of 'rack positions' that are associated with the unique ID of each rack.

Originally I was only going to store the occupied positions and calculate availability as users enter the equipment but now I am leaning towards generating all positions into the Positions table and mark them as either spare or filled. This decision was mainly based on the hierarchy of the database which goes: Location>Room>Aisle>Rack>Position>Equipment>Ports>Circuits all as tables with each linking back to its parent ID. So, in order to keep it consistent, I wanted to populate the positions table automatically each time a rack is entered. Since an average rack has 42 positions, this table will hold approx. 100K entries. Does anyone have any thoughts on this?

The way I wanted to do this was to count the records in the rack table in the rack entry form's FormOpen event and again at the OnClose event and have code generate the records as Spare into the Positions table for each of the new racks based on the rack size. Or is there an easier way to populate a table as records are entered into another?

Since the racks are rarely moved, I am not too concerned about deleting racks/positions. Equipment may move or be removed but the racks are fairly permanent.

This seems like something that has probably been done before (at least approximately) so any thoughts on this would be appreciated

Thanks
Hank

 

[CJ_London]

generating all positions into the Positions table and mark them as either spare or filled.

you probably do need a table for all positions, but you do not need a spare/filled flag - that can be determined from your equipment record - spare means rack record does not have a matching record in equipment table

 

[Hank.School]

Yes, I agree. Since I am fairly new to this, I sometimes forget that (especially considering the way I have it structured) the child entity will refer to the parent in a way that I don't need certain references in the parent. One of the biggest problems in DB design, at least for me, is translating from the way I think about things IRL to the most logical, structured way to represent them in Access. Thank you for the reply.