I am developing a web application for conducting Phishing Awareness testing (Github Repo). I have been getting tons of feedback on CR.SE to this project. It can be referenced if you want to get a more in-depth understanding of this project.
That being said, I am working at modifying and cleaning up my database to increase its efficiency for the server, readability for a DBA, and usability by the user, all while reducing the required overhead. Below is my proposed design of my database. Any suggestions as to the design of my database is appreciated.
To explain the project at a high level, I have a Graphical User Interface (GUI) that will allow users to log on and send emails out. These emails will be stored inside of organization units called projects. The user does not define who the email gets sent to. An algorithm is used to decide whether a mailing_list item is a valid recipient. If they are, an email is sent and it is logged in the sent_email table. Each email contains a webbug which references a public facing URL. This URL inserts data into the website_tracking and email_tracking tables based on where the request came from (the URL path).
+--------------------------+
| Tables |
+--------------------------+
| users |
| mailing_list |
| sent_email |
| projects |
| website_tracking |
| email_tracking |
| report_tracking |
| default_email_settings |
+--------------------------+
users
+---------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| USR_UserId | int | NO | PRI | NULL | AI |
| USR_Username | varchar | NO | | NULL | |
| USR_Email | varchar | NO | | NULL | |
| USR_FirstName | varchar | NO | | NULL | |
| USR_LastName | varchar | NO | | NULL | |
| USR_Initial | char(1) | YES | | NULL | |
| USR_Password | varchar(255) | NO | | NULL | |
| USR_CanCreateUsers | tinyint | NO | | NULL | |
+---------------------+------------------+------+-----+---------+-------+
mailing_list
+---------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| MGL_Id | int | NO | PRI | NULL | AI |
| MGL_Username | varchar | NO | | NULL | |
| MGL_Email | varchar | NO | | NULL | |
| MGL_FirstName | varchar | NO | | NULL | |
| MGL_LastName | varchar | NO | | NULL | |
| MGL_UniqueURLId | varchar | YES | | NULL | |
+---------------------+------------------+------+-----+---------+-------+
sent_email
+---------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| SML_EmailId | int | NO | PRI | NULL | AI |
| SML_UserId | int | NO | FK | NULL | |
| SML_ProjectId | int | NO | FK | NULL | |
| SML_SentTimestamp | Timestamp | NO | | NULL | |
+---------------------+------------------+------+-----+---------+-------+
projects
+------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| PRJ_ProjectId | int | NO | PRI | NULL | AI |
| PRJ_ProjectName | varchar | NO | | NULL | |
| PRJ_ProjectDescrip | varchar | NO | | NULL | |
| PRJ_ProjectAssignee | int | NO | FK | NULL | |
| PRJ_ProjectStart | Timestamp | NO | | NULL | |
| PRJ_ProjectLastActive | Timestamp | NO | | NULL | |
| PRJ_ProjectStatus | tinyint | NO | | NULL | |
+------------------------+------------------+------+-----+---------+-------+
website_tracking
+------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| WBS_WTrackId | int | NO | PRI | NULL | AI |
| WBS_WIp | varchar | NO | | NULL | |
| WBS_WHost | varchar | NO | | NULL | |
| WBS_WBrowserAgent | varchar | NO | | NULL | |
| WBS_WReqPath | varchar | NO | | NULL | |
| WBS_UserId | int | NO | FK | NULL | |
| WBS_ProjectId | int | NO | FK | NULL | |
| WBS_WAccessTimestamp | Timestamp | NO | | NULL | |
+------------------------+------------------+------+-----+---------+-------+
email_tracking
+------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| EML_ETrackId | int | NO | PRI | NULL | AI |
| EML_EIp | varchar | NO | | NULL | |
| EML_EHost | varchar | NO | | NULL | |
| EML_UserId | int | NO | FK | NULL | |
| EML_ProjectId | int | NO | FK | NULL | |
| EML_EAccessTimestamp | Timestamp | NO | | NULL | |
+------------------------+------------------+------+-----+---------+-------+
report_tracking
+------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| RPT_RTrackId | int | NO | PRI | NULL | AI |
| RPT_RIp | varchar | NO | | NULL | |
| RPT_RHost | varchar | NO | | NULL | |
| RPT_UserId | int | NO | FK | NULL | |
| RPT_ProjectId | int | NO | FK | NULL | |
| RPT_RAccessTimestamp | Timestamp | NO | | NULL | |
+------------------------+------------------+------+-----+---------+-------+
default_email_settings
+-------------------+------------------+------+--------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+--------+---------+-------+
| DFT_UserId | int | NO | PRI FK | NULL | |
| DFT_MailServer | varchar | NO | | NULL | |
| DFT_MailPort | varchar | NO | | NULL | |
| DFT_Username | int | NO | | NULL | |
| DFT_CompanyName | int | NO | | NULL | |
+-------------------+------------------+------+--------+---------+-------+
Best Answer
I think the passwords would normally be kept in a separate table and encrypted. Also you might need a column for number of failed password attempts with a limit and something to reset the value every 30 minutes etc.
I am guessing you would also have a stored procedure for the GUI to access passwords to prevent injection.