Database Technology
The database is created using SQLite3. Given that the project is an MVP it seemed suitable to go with the ease of use and size of a SQLite database.
Split
Similar to the APIs two halves of the database are kept completely separate. The product and authorization. The authorization contains one table. With the product containing four tables.
Authorization
The authorization database is used specifically to hold the clients login information.
It contains a single table called authClient the columns are: * clientName * password
The passwords are kept as hashed and salted encrypted versions.
Product
The product database holds all the information needed to complete the business logic of the entire application stack. It contains four tables.
User
The user table contains the user information. The columns are: * userName(clientName) * emailAddress
Where the userName is a primary key and matches the clientName in the authClient table.
Owns
The table contains the many to many relationship of users to devices. Since many users can own many devices and many devices can have many users this table is a necessary requirement to store that information. It contains two columns: * userName * deviceName
Device
This table contains the device information. It has the following columns: * deviceName (clientName) * devicePIN * deviceCommonName
The deviceName is the primary key and matches the clientName in the authClients table to allow devices to be clients of the product api as well. The devicePIN is the master pin that can be used many times by the user to retrieve their packages. DeviceCommonName being the name that will be displayed to the user on the chrome extension.
Delivery
This table contains the information for an individual delivery PIN. It contains the following columns: * deliveryID * deviceID * deliveryPIN
The delivery must know what device it belongs to hence the foreign key to the deviceName. It also contains a unique ID for the individual delivery to specifically access and delete it. The pin attribute being self explanatory.