Database
Table Structure
Home
> Software > Database Table Structure
Jump
to a Particular Table:
User
Table Structure | Parts Table Structure
| Checkout Table Structure
UPDATE
01.21.2003: The PartSubType field in the Users table has been
added. The purpose of the field is to allow greater discrimination in
parts searchs. For example, users can now search for only 74LS series
logic devices instead of all 74XX devices.
UPDATE
01.08.2003: The AccessLevel field in the Users table has been
changed. The field is now called Roles, and it contains 100-character
strings instead of 8-bit unsigned integers. This change was made to make
the database conform to the expectations of the ColdFusion MX IsUserInRole()
and GetAuthUser() security functions.
Table
Structure
The
database currently contains three tables. One table contains the list
of users and the associated access levels. The second contains the list
of parts. The third contains the list of users that have parts checked
out and what those parts are.
NOTE:
All information contained in this document is preliminary and
subject to change. In particular, the names of the tables and fields will
most likely be changed to conform to standard naming conventions.
User
Table Structure (back
to top)
The
user table contains information on each user of the system, including
authentication and access level information. The user table contains the
following fields:
| Field
Name: |
Field
Data Type: |
Purpose: |
UserID
(primary key) |
32-bit
unsigned integer |
Unique
user ID for each user. Students will use their 6-digit Bradley ID
number for their user IDs. Faculty will be assigned unique user
IDs differently. |
| Password |
20-character
string |
Up
to 20-character password for authentication. |
| FirstName
|
20-character
string |
User's
first name. |
| LastName
|
20-character
string |
User's
last name. |
| CollegeYear |
15-character
string |
One
of the string values 'Freshman', 'Sophomore', 'Junior', 'Senior',
'Graduate' or 'Faculty'. |
| LabSection |
10-character
string |
One
of the string values 'Tuesday', 'Thursday' or 'NA'. |
| Roles |
100-character
string |
One
of the string values 'Student', 'Door Warden', 'Lab Instructor', 'Parts
Manager', 'Administrator' or 'Designer'. This field was previously
an 8-bit unsigned integer field called "AccessLevel". The
change is required for the ColdFusion MX Server to administer security
properly. For more information, see User Groups
and Access Privileges. |
| LastLoginDate |
8-bit
ODBC date/time |
The
last date the user successfully logged in to EquipRD! |
More
fields may be added later as the need arises.
Parts
Table Structure (back
to top)
The
parts table contains information on the distinct parts in the equipment
room, such as part name, type, description and quantity. The database
also tracks how many parts are available, in use, reserved and damaged
awaiting repair.
| Field
Name: |
Field
Data Type: |
Purpose: |
| PartNumber
(primary key - part 1) |
20-character
string |
A
unique part number for identifying each distinct part in the equipment
room. |
| PartType
|
40-character
string |
A
device class, such as "Power Supply", "74XX Series
Logic", "A/D Converter", etc. |
| PartSubType |
40-character
string |
A
more specific device class, such as "DC Power Supply", "74LS
Series Logic, 8-bit A/D Converter", etc. |
| PartDescription
|
100-character
string |
The
description of a specific part, such as "HP E3630A Triple Output
DC Power Supply", "74LS00 Quad NAND Gates", "ADC0804E
8-bit A/D Converter", etc. |
| SerialNumber
(primary key - part 2) |
20-character
string |
The
manufacturer's serial numbers of parts that have serial numbers.
Alternatively, the ECE department's "EQ-xxxx" numbering
system could be used. |
| Quantity |
16-bit
unsigned integer |
For
parts without serial numbers, the number of distinct devices covered
under the Part Number. |
| Available |
16-bit
unsigned integer |
The
number of distinct devices available for use. |
| InUse
|
16-bit
unsigned integer |
The
number of distinct devices already checked out. |
| Reserved |
16-bit
unsigned integer |
The
number of distinct devices that have been reserved by a lab instructor
for an upcoming experiment. |
| Damaged |
16-bit
unsigned integer |
The
number of distinct devices that have been damaged and are awaiting
repair. |
| LastCheckoutDate |
8-bit
ODBC date/time |
The
last date when at least one of the part was checked out. |
More
fields may be added later as the need arises.
Checkout
Table Structure (back
to top)
The
checkout table contains information on which users have which parts checked
out. Each user-part combination gets its own record in the table.
| Field
Name: |
Field
Data Type: |
Purpose: |
| Row
ID
(primary key) |
16-bit
unsigned integer |
Generates
a primary key for the table, since the other fields are not guaranteed
to contain a unique entry in each record. |
| User
ID |
32-bit
unsigned integer |
User
ID from User Table |
| PartNumber |
20-character
string |
Part
Number from Parts Table |
| SerialNumber |
20-character
string |
For
serialized parts, SerialNumber from Parts Table. |
| Quantity |
16-bit
unsigned integer |
The
number of distinct devices matching PartNumber checked out out to
the user specified in UserID. |
| CheckoutDate |
8-bit
ODBC date/time |
Date
and time part was checked out. |
More fields
may be added later as the need arises.
|