Data Modelling for White Board app
In the journey of my internship at Hasura, as in the first week I had created the prototype for my app idea White Board, the next step was to make relational models for my app the first task in third week was to make data models for my app and to add relevant tables using the console UI of local development.
Data models
Data models define the description of data structure and the way data are organized using a database. It define how data is connected to each other and how they are processed and stored inside the system. Data Models are fundamental entities to introduce abstraction in a DBMS.
Data model should fulfil the requirements of the for which it is being created, with requirements I mean to visualize the necessary tables and relations between various tables, depending on what are the services app is giving. Once all things about data models are clear, we can proceed to integration of our app with front-end and back-end.
How did I proceed
Having all those things in my mind, I first taken a rough idea of what are the various data required and how my app will be fetching and updating the data from/to database. After getting a rough idea, I headed up to PonyORM.
Pony is an advanced object-relational mapper. An ORM allows developers to work with the content of a database in the form of objects. Pony ORM is a library for Python language that allows you to conveniently work with objects that are stored as rows in a relational database.
Using the online database schema editor provided by PonyORM, I was able to visualize my database schema for my app.
In above picture, basic data modelling for my app is shown. There are basically five tables in the database, a short description for each one is below.
User_info
Basic information of a user is stored on this table. All info stored at the time of sign up a new user. These information can be updated later on profile page. A id is primary key for the user_info. email and contact are required information for each user. Hence each user is created through Hasura auth API, so I don’t need to store all the basic information here, just id and name does work too. Name field was not present in hasura_user table, also we can’t add foreign keys to that table so needed a new table to store those info.
Group
My app provide feature to create a group of users. The information related to group is stored on this table. A group basically has group_id, group_name and a created timestamp at which group was created. There is an attribute name wb_link, which is for the link of White Board associated with each group.
Group_user
This table maps users to tables, it is a many-to-many relationship. Set of group_id and user_id is primary key. There is another attribute named as leader, which is for group admin is a boolean value. If leader === false, that user is a member and if leader === true, that user is one of the group admin.
Messages
This table stores the information of messages of group chat. Each message has a group_id and user_id, and set of these two attributes is not a primary key, because a user can send same message in same group multiple times. Each message also have a created timestamp, which is usefull to sort message by time. user_id and group_id are foreign keys to tables user_info.id and group.id respectively.
Attachment
This table stores the information about the attachments send to a group. An attachment such as image, video, pdf etc require to store name of attachment and the link where those are stored. Each attachment is a message, in which attachment.message_id is foreign key to message.id and name of the attachment file is text of the message.
These were the tables required to power the app.
I had created these tables for my app on local hasura platform using the console and added foreign key constraints respectively.
Comments
Post a Comment