Data Modelling for ToteBag

Before you proceed, a quick recap on how ToteBag came about to be and how I am planning to build ToteBag.

Data modelling is a crucial step to building an app. Getting this right will eliminate a lot of potential problems during the development phase. The ER diagrams can created quite easily using Pony ORM. In addition, Pony ORM can also generate queries based on the database you are using.

My app will require 3 different data tables — resource, request, user

The resource table will contain information about all the resources of all the different users.

The request table contains information of resources requested by the various users, along with details like when the request was made and status of the request, among other things.

The user table will contain user information, so as to establish foreign key constraints.

Resource Table: The following are the essential fields in the table

• resourceID — a unique field to identify every record uniquely, a.k.a the primary key
• ownerID — a field to denote the user who uploads the resource, a.k.a a foreign key to the User table
• title — to denote the name of the resource
• author_artist — to denote the author of the book or the artist who made a movie or a music album
• category — either one of book, music or movie
• quality — to denote the nature of the resource. In case of books, it will be used to denote whether a book is a paperback or a hardcover; in case of movies, it can be used to denote if it is a Bluray version, etc
• genre — to denote the genre of the book, music or movie. e.g. — fiction, technology, sci-fi, drama, rock, pop, fusion, etc.

These are additional fields for the table, so that I can expand the functionality of my app, in the future

• year — in case I want to add a filter feature later on, the year can be used to filter out to albums or movies with same names depending on the year of publishing
• language — to filter out resources based on languages
• numRequests — to maintain a count of how many pending requests a particular resource has
• lentTo — to keep track of whom the resource has been lent to, in case new requests start coming in for the same resource and the new requestor need to be notified that his request is being waitlisted
• publication_production — in case someone is looking for a special edition/copy, as it is the case quite often with books

User Table: This table will keep track of the various user details.

• userID — to uniquely identify every user, primary key
• name — the name of the user
• username — a handle for the users in the app
• emailID — to provide contact details via email and notify the status of requests
• phone — to provide a means for other users to contact this user, if required
• resource — a list of all the resources this user owns

Request Table: The various requests made are stored in this table.

• requestID — to uniquely identify every request made, primary key
• resourceID — to identify the resource that is being requested
• requestorID — the user that is making the request
• requesteeID — the user that owns the resource being requested
• status — the status of the request; can have one of these values: Pending, Approved, Declined; default status is Pending
• createdOn — the date and time when the request was made.
• approvedOn — the date and time when the request was approved/deleted.

Foreign Key Constraints: The following foreign key constraints exist

• Resource : ownerid → User : userID
• Resource : lentTo → User : userID
• Request : resourceID → Resource : resourceID
• Request : requestorID → User : userID
• Request : requesteeID → User : userID