0

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    |       |
+-------------------+------------------+------+--------+---------+-------+
9
  • 1
    No foreign keys defined? The output of SHOW CREATE TABLE name; is far more useful than DESCRIBE TABLE by the way. Jul 26 '16 at 16:12
  • @ypercubeᵀᴹ while I do agree with you there, I haven't yet created this database with these new fields. This is purely based on whiteboard drawing right now and I'm just checking my work. As far as foreign keys, FK is foreign key but I didn't want to drag the data to multiple rows so I left it out of extra. If you think it should be shown, I can add it in.
    – Trojan404
    Jul 26 '16 at 16:33
  • Use SHOW CREATE TABLE name\G - gives a good format for copying and pasting. Your schema looks OK - but this is too "big" a question for here - you need to start working on your system and as more specific problems arise, you can ask about them here. There's nothing obviously wrong - but starting to code is what's needed now.
    – Vérace
    Jul 26 '16 at 16:46
  • Yes, sorry I missed the FKs. For a [database-design] question, I expect this info to be in the question, as Verace and I advised, with SHOW CREATE TABLE. It still may be closed as out of topic but lets see. Jul 26 '16 at 16:49
  • @Vérace: That's kind of what I'm looking for. If you look at some of the stuff on the CR linked questions, I've got about 2 months of work on this project already. Now I'm just redesigning my database to be better designed and utilized. However, as both of you and ypercube have said, use SHOW CREATE TABLE. I would but this is whiteboard drawing so there is no built database to query from. We'll see what happens, but hopefully I can at least just get some sort of broad suggestion on improvements to the structure.
    – Trojan404
    Jul 26 '16 at 16:50
1

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.

1
  • The password is hashed and uses a hash_veirfy function to check if it is valid. This function also checks for injection. Thanks though!
    – Trojan404
    Jul 26 '16 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.