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.
Best Answer
Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role.
Assign rights to the roles:
Then, assign these roles to users
This should not be the case, if the database has been designed carefully. If you are using referential integrity, the users should not able manipulate the data in a way that corrupts the application. If you have a table
employee
that refers tostate
, then define a foreign key constraint on this column:With this constraint it is not possible to delete a state or change its
id
as long as it is referenced inemployee
.A completely different way of solving this problem, is to not have a lookup table (or view) at all in your db, but to keep the lookup information within your application (e.g. as embedded xml resource).
Yet another solution:
Create the table as another oracle owner and grant the select right to your usual application owner. Here you can then create a view.
or you could just create a synonym (as
appOwner
):In both cases your users must not have access to
otherOwner
.