As promised.
Table Definition
users
| Column Name | Data Type | Constraints |
| id | integer | Primary Key |
| handle | varchar(10) | Unique Key |
| fname | varchar(15) | NOT NULL |
| lname | varchar(15) | NOT NULL |
| password_hash | varchar(128) | NOT NULL |
| password_salt | varchar(8) | NOT NULL |
| email | varchar(45) | NOT NULL |
handle – name of the account
fname – first name of user
lname – last name of user
password_hash – SHA512 digest of user’s plaintext password
password_salt – 8 bit randomly generated salt
email – email of user
members
| Column Name | Data Type | Constraints |
| id | integer | Primary Key |
| project_id | integer | Foreign Key |
| user_id | integer | Foreign Key |
| permission | varchar(10) | NOT NULL, Default “guest” |
id – primary key
project_id – project the member belongs to
user_id – user that has access to the project
permission – the permission level (admin, member, guest)
projects
| Column Name | Data Type | Constraints |
| id | integer | Primary Key |
| name | varchar(30) | Primary Key |
| description | varchar(100) | NOT NULL, Default “” |
id – primary key
name – name of the project
description – description of the project
jobs
| Column Name | Data Type | Constraints |
| id | integer | Primary Key |
| project_id | integer | Foreign Key |
| name | varchar(30) | NOT NULL |
| description | varchar(100) | NOT NULL, Default “” |
id – primary key
project_id – name of the project it belongs to
name – name of the job
description – description of the job
etransactions
| Column Name | Data Type | Constraints |
| id | integer | Primary Key |
| date | date | NOT NULL, Default Timestamp |
| job_id | integer | Foreign Key |
| counterparty | varchar(30) | NOT NULL |
| category | varchar(30) | NOT NULL, Default “Other” |
| flow | varchar(10) | NOT NULL |
| created_by | integer | Foreign Key |
| description | varchar(100) | Default “” |
| amount | decimal(9,2) | NOT NULL, Unsigned |
id – primary key
date – date of the transaction
job_id – the job which the transaction belongs to
counterparty – the transaction made to
category – the category of the transaction
flow – inflow or outflow
created_by – the user who created the transaction
description – description of the transaction
amount – the amount of the transaction