-
Notifications
You must be signed in to change notification settings - Fork 0
Database Schema Information
This page contains our MySQL database schema. The Database is named SEAC_Tool_Shed
. Below is our E-R Diagram:
Most tables must be populated with at least one record for the application to function. These tables are marked with a dagger (†).
-
Accounts
† -
Current_Membership_Status
† -
Genders
† Gift_Cards
-
Membership_Levels
† -
Privilege_Levels
† -
Waivers
†
-
Transactions
† -
Transaction_Types
† Transaction_Payments
-
Brands
† -
Categories
† -
Tool_Categories
† -
Tool_Locations
† -
Tool_Statuses
† -
Tool_Types
† -
Types
† -
Tools
†
The Accounts
table contains individual account information. It connects with the Current_Membership_Status
, Genders
, Gift_Cards
, Membership_Levels
, Privilege_Levels
, and Transactions
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Account_ID | INT | The primary key of the table. | PRIMARY KEY NOT NULL AUTO_INCREMENT |
Customer_ID | VARCHAR(40) | The ID for the Account's Square customer data. | |
First_Name | VARCHAR(255) | The Account's first name. | NOT NULL |
Last_Name | VARCHAR(255) | The Account's last name. | NOT NULL |
DOB | DATE | The Account's birth date. | NOT NULL |
Gender_Code | TINYINT UNSIGNED | The integer code of the Account's gender; connects with the Genders table as a foreign key. |
NOT NULL UNIQUE |
Organization_Name | VARCHAR(255) | The Account's optional Organization Name. | DEFAULT NULL |
VARCHAR(255) | The unique email associated with an Account. | NOT NULL UNIQUE | |
Password | VARBINARY(255) | The string value of an Account's password, stored and verified using Advanced Encryption Standard. | NOT NULL |
Phone_Number | CHAR(10) | The 10-digit code of an Account's associated phone number. | NOT NULL |
Address_Line1 | VARCHAR(255) | The first line of an Account's address. | NOT NULL |
Address_Line2 | VARCHAR(255) | The optional second line of an Account's address. | |
City | VARCHAR(255) | The name of the city associated with the address. | NOT NULL |
State | VARCHAR(255) | The name of the state associated with the address. | NOT NULL |
Postal_Code | CHAR(5) | The postal code associated with the address. | NOT NULL |
Secondary_First_Name | VARCHAR(255) | An optional secondary first name. | |
Secondary_Last_Name | VARCHAR(255) | An optional secondary last name. | |
Secondary_Email | VARCHAR(255) | An optional secondary email. | |
Secondary_Phone_Number | CHAR(10) | An optional secondary 10-digit phone number. | |
Account_Creation_Date | DATE | The current date on the day of the Account's creation. | NOT NULL DEFAULT (CURRENT_DATE()) |
Account_Notes | TEXT | Any notes for the Account that may be important for staff. Not visible to the holder of the Account. | |
Membership_Level | TINYINT UNSIGNED | The integer code value associated with the Account's Membership Level; connects with the Membership_Levels table as a foreign key. This field is populated upon membership sign-up. |
NOT NULL |
Membership_Status | TINYINT UNSIGNED | The integer code value associated with the active/inactive status of an Account, default Active; connects with the Current_Membership_Status table as a foreign key. |
NOT NULL DEFAULT '1' |
Membership_Auto_Renewal | BOOL | The boolean value associated with auto membership renewal, default false . |
NOT NULL DEFAULT '0' |
Membership_Creation_Date | DATE | The date when a membership is activated, defaulted to the current date when Registration status is set; updates when a new membership is selected. | NOT NULL DEFAULT (CURRENT_DATE()) |
Membership_Expiration_Date | DATE | The date when a membership expires, defaulted to the current date when Registration status is set plus one year; updates once a new membership is selected. | NOT NULL DEFAULT (DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR)) |
Privilege_Level | TINYINT UNSIGNED | The integer code value associated with the privilege status of an Account; connects with the Privileges_Level table as a foreign key. 1 = Customer, 2 = Volunteer, 3 = Employee, 4 = Manager, 5 = Administrator. Defaults to Customer unless specified by an Employee or higher. |
NOT NULL DEFAULT '1' |
The Brands
table contains tool brands, such as Makita
or DeWalt
. It connects with the Tools
table.
Field | Type | Purpose | Details |
---|---|---|---|
Brand_Name | VARCHAR(255) | The primary key of the table, and the name of the brand. | PRIMARY KEY NOT NULL |
The Categories
table contains tool categories, such as Carpentry
or Roofing
. It connects with the Tools
table.
Field | Type | Purpose | Details |
---|---|---|---|
Category_ID | INT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT |
Category_Name | VARCHAR(255) | The name of the category. | NOT NULL |
The Current_Membership_Status
table contains the two membership statuses, Active
and Inactive
. It connects with the Accounts
table.
Field | Type | Purpose | Details |
---|---|---|---|
Membership_Status | TINYINT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL UNIQUE |
Membership_Status_Description | VARCHAR(255) | An optional description of a Membership Status. | DEFAULT NULL |
The Genders
table contains genders, such as Female
and Non-Binary
. It connects with the Accounts
table.
Field | Type | Purpose | Details |
---|---|---|---|
Gender_Code | TINYINT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT |
Gender_Name | VARCHAR(255) | The name of the gender. | NOT NULL |
The Gift_Cards
table contains Gift Card data, such as the Membership Level of the gift card and an Is_Applied
flag. It connects with the Accounts
and the Membership_Levels
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Account_ID | INT UNSIGNED | A primary key of the table; the unique ID of a gift card's assigned Account. Connects with the Accounts table as a foreign key. |
PRIMARY KEY NOT NULL UNIQUE |
Card_Code | CHAR(6) | A primary key of the table; the randomly-generated 6-digit activation code of the gift card, emailed to the Account. | PRIMARY KEY NOT NULL DEFAULT lpad(floor((rand() * 999999.99)),6,_utf8mb4'0') |
Membership_Level | TINYINT UNSIGNED | The integer code value associated with the gift card's Membership Level; connects with the Membership_Levels table as a foreign key. |
NOT NULL |
Is_Applied | BOOL | A flag indicating whether a gift card has been redeemed already, default false . |
NOT NULL DEFAULT '0' |
The Membership_Levels
table contains membership levels, such as Builder
and Contractor
. It connects with the Accounts
and Gift_Cards
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Membership_Level | TINYINT UNSIGNED | The primary key of the table; connects with the Accounts table. Stores the unique ID for each membership level: 1 is Tinkerer , 2 is MacGyver , etc. |
PRIMARY KEY NOT NULL AUTO_INCREMENT |
Membership_Title | VARCHAR(255) | The name of the membership level, such as MacGyver . |
NOT NULL |
Membership_Price | FLOAT | The annual purchase price of the membership. | NOT NULL |
Max_Tool_Checkout | INT UNSIGNED | The maximum number of tools that can be checked out under the membership level. | NOT NULL |
Is_Organizational | TINYINT(1) | A flag indicating whether a membership level is organizational, and therefore invisible to non-organization Accounts. | NOT NULL |
The Privilege_Levels
table contains the privilege levels accounts can have, such as Manager
and Admin
. It connects with the Accounts
table.
Field | Type | Purpose | Details |
---|---|---|---|
Privilege_Level | TINYINT UNSIGNED | The primary key of the table; connects with the Accounts table. |
PRIMARY KEY NOT NULL |
Privilege_Title | VARCHAR(255) | The name of the privilege level, such as Customer . |
NOT NULL |
The Tool_Categories
table contains tool categories, such as Crafting
and Masonry
. It connects with the Categories
and the Tools
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Tool_ID | INT UNSIGNED | A primary key of the table; connects with the Tools table as a foreign key. |
PRIMARY KEY NOT NULL |
Category_ID | INT UNSIGNED | A primary key of the table; connects with the Categories table as a foreign key. |
PRIMARY KEY NOT NULL |
The Tool_Locations
table contains tool locations, such as Mobile Unit - Thomas P. Ryan Center (Monday)
and Main Location
. It connects with the Tools
table.
Field | Type | Purpose | Details |
---|---|---|---|
Tool_Location | INT UNSIGNED | The primary key of the table; connects with the Tools table to check on Current_Location and Home_Location . |
PRIMARY KEY NOT NULL AUTO_INCREMENT |
Location_Name | VARCHAR(255) | The name of the location. | NOT NULL |
The Tool_Statuses
table contains tool statuses, such as Checked Out
and Maintenance
. It connects with the Tools
table.
Field | Type | Purpose | Details |
---|---|---|---|
Tool_Status | TINYINT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL |
Tool_Status_Details | VARCHAR(255) | The label of the tool status, such as Available . |
NOT NULL |
The Tool_Types
table contains tool types, such as Angle Grinders
and Jigs
. It connects with both the Tools
and the Types
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Tool_ID | INT UNSIGNED | A primary key of the table; connects with the Tools table as a foreign key. |
PRIMARY KEY NOT NULL |
Type_ID | INT UNSIGNED | A primary key of the table; connects with the Types table as a foreign key. |
PRIMARY KEY NOT NULL |
The Tools
table contains tool data objects. It connects with the Brands
, Tool_Categories
, Tool_Locations
, Tool_Statuses
, and Tool_Types
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Tool_ID | INT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL AUTO_INCREMENT |
Old_Tool_ID | VARCHAR(255) | A field used for data migration. | DEFAULT NULL |
Tool_Name | VARCHAR(255) | The name of the tool. | NOT NULL |
Brand_Name | VARCHAR(255) | The brand of the tool; connects with the Brands table as a foreign key. |
DEFAULT NULL |
Tool_Weight | FLOAT | The weight of the tool, in pounds. | DEFAULT NULL |
Tool_Size | VARCHAR(255) | The size of the tool, in inches. | DEFAULT NULL |
Home_Location | INT UNSIGNED | The location where the tool is meant to be returned; connects with the Tool_Locations table as a foreign key. |
NOT NULL |
Current_Location | INT UNSIGNED | The current location of the tool, which will be the same as Home_Location unless the tool has been classified as Floating ; connects with the Tool_Locations table as a foreign key. |
NOT NULL |
Location_Code | VARCHAR(255) | The descriptor of the tool's current, specific location, such as In Drawer 4C . |
DEFAULT NULL |
Tool_Description | TEXT | Details regarding the tool. | |
Tool_Status | TINYINT UNSIGNED | The status of the tool, such as Available or Checked Out ; connects with the Tool_Statuses table as a foreign key. |
NOT NULL |
Tool_Link | VARCHAR(2000) | The link to a tool's image. | DEFAULT NULL |
Tool_Manual | VARCHAR(2000) | The link to a tool's manual. | DEFAULT NULL |
Tool_Loan_Fee | FLOAT | The monetary value of loaning a tool, in USD ($xx.xx). | NOT NULL DEFAULT '0' |
Default_Late_Fee | FLOAT | The monetary amount to be charged to a user every day a tool is overdue, until either returned or fully charged. | NOT NULL DEFAULT '1' |
Default_Loan_Length | TINYINT UNSIGNED | The length of a tool's loan period before it must be returned, defaulted to seven days. | NOT NULL DEFAULT '7' |
Renewal_Amount | TINYINT | The amount of times a loan may be extended, defaulted to once. | NOT NULL DEFAULT '1' |
Tool_Replacement_Cost | FLOAT | The monetary cost to replace a tool entirely. | NOT NULL |
Is_Floating | BOOL | A flag indicating whether a tool must be returned to its home location. | NOT NULL |
Is_Featured | BOOL | A flag indicating whether a tool is featured on the main page. | NOT NULL DEFAULT '0' |
The Transaction_Payments
table contains payment invoices types for any transaction that occurs in the system. It connects with the Transactions
table.
Field | Type | Purpose | Details |
---|---|---|---|
Transaction_ID | INT UNSIGNED | The primary key of the table. Relates to a transaction in the Transactions table. |
PRIMARY KEY NOT NULL |
Invoice_Number | VARCHAR(255) | The primary key of the table. The invoice number generated for each transaction. | PRIMARY KEY NOT NULL |
Payment_ID | VARCHAR(2000) | The invoice code generated from Square for each transaction. | NOT NULL |
The Transaction_Types
table contains transaction types, such as Membership Renewal
and Tool Return
. It connects with the Transactions
table.
Field | Type | Purpose | Details |
---|---|---|---|
Transaction_Type | TINYINT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL |
Transaction_Details | VARCHAR(255) | The label of the transaction type, such as New Membership . |
DEFAULT NULL |
The Transactions
table contains transaction data objects. It connects with the Tool_Transactions
, Transaction_Types
, and Accounts
tables.
Field | Type | Purpose | Details |
---|---|---|---|
Transaction_ID | INT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL AUTO_INCREMENT |
Account_ID | INT UNSIGNED | The Account ID or IDs associated with the transaction; connects with the Accounts table as a foreign key. |
NOT NULL |
Transaction_Date | DATE | The date the transaction took place. | NOT NULL |
Transaction_Type | TINYINT UNSIGNED | The type of the transaction; connects with the Transaction_Types table as a foreign key. |
NOT NULL |
Start_Date | DATE | The potential start date of the transaction. | DEFAULT NULL |
End_Date | DATE | The potential end date of the transaction. | DEFAULT NULL |
Check_Out_Date | DATE | The potential check-out date of the transaction. | DEFAULT NULL |
Check_In_Date | DATE | The potential check-in date of the transaction. | DEFAULT NULL |
Payment_Amount | FLOAT | The potential payment total of the transaction. | DEFAULT NULL |
The Types
table contains tool types, such as Axes
and Screwdriver Sets
. It connects with the Tool_Types
table.
Field | Type | Purpose | Details |
---|---|---|---|
Type_ID | INT UNSIGNED | The primary key of the table; connects with the Tool_Types table. |
PRIMARY KEY NOT NULL AUTO_INCREMENT |
Type_Name | VARCHAR(255) | The name of the tool type, such as Table Saws . |
NOT NULL |
The Waivers
table contains the waiver data objects necessary for the SEAC Tool Shed. Currently, those are the Tool Waiver and Indemnification
and Tool Lending Agreement
.
Field | Type | Purpose | Details |
---|---|---|---|
Waiver_ID | INT UNSIGNED | The primary key of the table. | PRIMARY KEY NOT NULL |
Waiver_Name | VARCHAR(255) | The name of the waiver, such as Tool Waiver and Indemnification . |
NOT NULL |
Waiver_Details | TEXT | The text of the waiver. | NOT NULL |
SEAC Toolshed project by The Handymen: Ian Dinga, Andy Erskine, Fei Gao, Evan Hiltzik, Bryce Hofstrom, Michael Pacholarz, and Aryan Todi.
Special thanks to Andrew, Mike, and Nick.
- Privileges, User Levels, and Access Levels
- Database Schema Information
- User Interface
- Project Directory Structure
- Data Migration
- Create an account as a customer
- Create an account for another person as a privileged user
- Editing your own account information
- Editing the account information of another person as a privileged user
- Deleting an account