Updated Database Design

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