Below is a description of the two databases used through June,
2001 by me in the Queens College lock shop. I used FoxPro as my tool,
which enabled me to do easily what was required. Example: when the
abbreviation of Margaret Kiely Hall was changed from "MKH" to "KY", the commend
"REPLACE ALL BLDG WITH 'KY ' FOR BLDG='MKH ' changed all of the several
hundred records in less than 30 seconds, and updated the indices as well.
I do not guarantee the completeness of the special symbols used.
I examined most of the over 10,000 records in backup copies from June, 2001 to create
this page, but changes may have been made since my retirement, also some may
have slipped by me, please feel free to email questions to
.
Location Database
This database is really a lock database based on
location, containing information of all locks permanently attached
to a physical location. Examples include doors, lecterns, telephone
cabinets, fire alarm panels, guard booths, etc. Vehicle locks could
have also be included (although the vehicles aren't permanently located, their
locks are fixed to specific vehicles.)
Field
Name
Purpose
Notes and Comments
Bldg
Building abbreviation
(standard college if available, otherwise created)
Indexed on the combination of
BLDG
+ ROOM,
which makes each
record unique within the database. I indexed on TRIM
(BLDG) + " " + ROOM which removed trailing spaces from the BLDG field,
added a space, then ROOM. This enabled me to FIND a room without
worrying about spaces.
Room
Room number
Special symbols
<
from
>
to (exterior door only)
-
door between room; lock direction uncertain.
#
deadbolt lock (key required from either side)
' (apostrophe)
indicates one of multiple keys which will open door
EXT>n
exterior door to n floor
If no special symbol, inferred to be corridor and/or
only door to room.
Room is described as much as
necessary to make it unique within the building. Examples:
101 E UPPER would be the upper lock on the
east door of room 101; 103<102E would be
room 103 through the door from room 102E; 105 FAP
would be the fire alarm panel in room 105; EXT>2
MIDDLE SOUTH would be the exterior to the second floor south side
group of doors - the middle door. Occasionally a comment (e.g.
"was 323")
to indicate a previous room number which was changed.
Since the exterior of a building is
all the same area (and a key is required to enter the building), they alone use the ">" symbol; this is so they all
group together in the listing and all exterior door keying for any
building is easily
determined.
A special symbol in the initial position also sometimes used to change sort order, so specific doors
appear in other than their normal sort position; e.g. "'fire" sorts to the beginning of a building,
"_fire" sorts to the end (space may also be used for this purpose.)
Leading zeros inserted into KY room numbers to provide proper sorting
(e.g. 0090 before 1200), and zero substituted for a letter in some
building basements to sort
basement rooms before upper floors.
Hook
Hook number of key which operates lock
Special symbols
.#
door permanently closed
.-
no key required from either side
.=
no door (open space)
, (comma)
opened by multiple keys
&
opened by multiple keys
inv
inverted cylinder - not functioning but present
.xyz*
not a hook number; xyz are alpha-numerics of up to length 8
Hook numbers may have alphabetic suffixes to indicate different keys
which hang on the same hook. Different keys should never have identical hook numbers.
Non-hook numbers (.xyz) indicate special circumstance where key is
not hanging on a hook; can be a manufacturer's number or other value
generated in the lock shop.
Was
Previous hook number
If no entry,
underscores (________) provided for manual entry on printout when key is
changed.
Mast
Master key which opens lock (if any)
Special symbols
^
high security
^^
very high security
-^
no master at all
*
manufacturer's number
=
no door or no lock
?
unsure
Masters generally have letters for hook numbers. May also contain specific mastering info (Best & Keymark).
base
Key at main gate to open this door
Not up-to-date.
Date
Date of last lock change
If blank, not changed since 1983.
Dept
Department assigned to room
Underscores if department unknown (again, for manual entry
on printout.)
Desc
Description of room function
When building is first entered,
Desc is from blueprints, changed as necessary as room
function changes. Any notes or additional description also included.
Arch_room
Room number on architect's plans
Indexed on combination with BLDG.
Most useful when referring to original building blueprints.
Arch_door
Door number on architect's plans
Indexed on combination with BLDG.
Most useful when referring to original building blueprints.
PAGE
Page referred to on hardware schedule of building
Used primarily during initial setup of a building;
rarely used after building has been in service for a year or more.
HS
High Security
Not used (included within Mast);
sometimes a "scrap" field
Location
Indices
These are what controls the sort order in display and
printouts of the LOCATION database.
The FoxPro command is INDEX ON EXPRESSION
TO INDEX NAME where the
index and expression are listed below. To change the sort order, the
command is SET INDEX TO
INDEX NAME , e.g.
SET INDEX TO ROOM would sort on the room index. Only two
printouts were regularly done, ROOM and HOOK
Index Name
Expression
Notes and Comments
ROOM
TRIM(BLDG) + " " + ROOM
TRIM() removes
trailing blanks from the field, enabling a search without regard to the
length of the building field, .e.g. FIND
JH 010 would find the proper record without
adding two blanks to follow the JH.
HOOK
IIF(VALUE (HOOK<1), HOOK, VALUE(HOOK))
VALUE() determines the numerical value of the enclosed expression,
and stops when reaching a letter. This expression translates to "IF THE VALUE OF HOOK IS LESS THAN 1, INDEX ON HOOK [the standard left
to right character method], OTHERWISE INDEX ON THE NUMERIC VALUE OF HOOK."
This placed all non-hook numbers in the
front of the index in proper order (since they are all less than 1 because they
begin with a dot), the
rest fell into their proper order
further back. This method also places each record in its proper
place regardless of leading zeros.
Mast
Mast
Normal index.
WAS
WAS
Normal index, no special sorting, never printed. Used to
determine where a key was last used, if not currently in use.
A_ROOM
TRIM(BLDG) + " " + Arch_room
Most useful when referring to original building blueprints.
Occasionally printed a particular building when requested by Campus
Facilities when they needed a listing of architectural rooms vs. current
room numbers.
A_DOOR
TRIM(BLDG) + " " + Arch_door
Most useful when referring to original building blueprints.
The second database contains descriptions of the keys.
Hook Database
This is really a key
database, which contains primarily physical descriptions of
keys cross-referenced by hook numbers, although there are some which reference manufacturer's numbers
Field
Purpose
Notes and Comments
Hook
Hook number. May include alphabetic suffixes and
special symbols.
Special symbols
^
high security
^^
very high security
-^
no master at all
*
manufacturer's number
&
opened by multiple keys
?
unsure
code
Master code.
keyway
Manufacturer's designation of the keyway (the shape of the
cross-section of the key; sometimes the length of key as well.)
Indexed with
pinning to produce unique record.
pinning
Physical description of cuts of key.
Uses manufacturer's depth of cuts for the particular key, in the order and
depths specified by the manufacturer. Order could be bow-to-tip or
tip-to-bow, and depths 0-9 or 1-0. and are 5, 6 or 7 numbers
Letters used to indicate non-standard depths.
Hook
Indices
These control the sort order in display and
printouts of the HOOK database. The database was never totally printed
out; the indices are primarily for display use.
Index
Expression
Notes and Comments
Hook
Hook
Normal index.
Mast
code
Normal index.
info
TRIM(KEYWAY) + " " + PINNING
Used to find order of keying and unused pinnings; also to find the
hook number if I had a unmarked key (from which I could determine the
keyway and pinning by examination.) This application was vital if an
unauthorized person was found with unmarked key; I could find out where
this person had access.
Lockshop Databases
.
