Skip to main content

Table Schema

Continuing my last post.

When using SQL database a very important aspect is the table schema. What is the best schema for a table. I feel we can never say this definitely we can only try making it better. We had 2 choice when deciding on the schema for the notification app. 
  1. One notification table for all types of notification (Owner, Group and Watching)
  2. Three notification table for 3 types of notification
We decided to go with the second option. You ask why. The needs of the three notification are totally different. For example, owner notification needs to be related to User table while Group notification needs to be related to the group table. If we had used option 1 we would have many columns with null values for a large set of rows (remember this is SQL not NoSQL) creating a table with sparse data.
Owner notification table schema is as follows:

Column Name Data Type Data Range Description
message String Notification message
type Enum create, upload, update, delete Type of notification
owner_email String Foreign key for user table
resource Enum graph, layout, group Type of notification's resource
resource_id Integer ID of the resource in resource's table
is_read Boolean Mark if the notification is read or not; Default: False
is_email_sent Boolean Mark if an email has been sent for the notification; Default: False
emailed_at Time Time the email is sent for the notification
created_at Time Notification create time
updated_at Time Notification update time

An interesting point about this schema is that instead of having a foreign key relationship with different resource (graph, layout and group) we specify just 2 columns for holding the name and ID of the resource. This creates a table with dense data.

Group notification table schema is as follows:
Column Name Data Type Data Range Description
message String Notification message
type Enum share, unshare, add, remove Type of notification
owner_email String Email of the user who created the notification
group_id Integer Foreign key for group table
group_member String Foreign key for User table (group member)
resource Enum graph, layout, group Type of notification's resource
resource_id Integer ID of the resource in resource's table
is_read Boolean Mark if the notification is read or not; Default: False
is_email_sent Boolean Mark if an email has been sent for the notification; Default: False
emailed_at Time Time the email is sent for the notification
created_at Time Notification create time
updated_at Time Notification update time

When a group notification is created, we create notification record for every group member in the group notification table. This is needed so we can save 'is_read' and 'is_email_sent' states for different group members. Another way of saving states for different users is by having a relationship table which saves these states. The relationship table will have foreign key constrains with User, Group and GroupNotification table. 

Comments

Post a Comment

Popular posts from this blog

Django + Kafka

Q & A of connecting Apache Kafka and Django Problem #1 Kafka consumer should always be listening for new messages in the queue. The consumer should be running in parallel to GraphSpace app (producer). Solution There are multiple ways to do this. We create another Django project say called "GraphSpace notification consumer" which starts along the GraphSpace application and establishes a connection with Kafka. This is an overkill for a simple consumer. We can use celery for multi-threading. Celery itself uses Redis or RabbitMQ as a queue for tasks. This setup might work for a large scale multi-threading system but for a simple setup of running a consumer this is a overkill. We will be running 2 queues (Redis and Kafka), one for queuing task and another for queuing content. I tried this setup with the following architecture: I will recommend keeping the architecture simple, run the consumer code on a different thread in daemon mode. Problem #2 Where
2 comments
Read more

Websockets in Django

Q & A What are websocket? Why do we need it? Quoting from wikipedia , "WebSocket is a computer communications protocol, providing full-duplex communication channels over a single TCP connection. The WebSocket protocol was standardized by the IETF as RFC 6455 in 2011, and the WebSocket API in Web IDL is being standardized by the W3C." WebSockets are used for streaming messages. To implement real-time notification we need to stream messages from the server to the client without a refresh or making an HTTP request from the client. Hence websockets. How do we implement websockets in django? We will be using django-channels  (channels) for websockets. Django, by default does not support websocket. Channels is a django project which allows Django to handle websockets, HTTP and HTTP2 requests. But how does channels implement websockets in WSGI server (gunicorn) which does not support websockets. Simple we don't. Instead, we will be using  Daphne , an interface serv
3 comments
Read more

Kafka Clients 101

In this post, we will look at packages to build a real time notification system for GraphSpace. GraphSpace is built in Django. The requirements for our system are Decoupled from the main app ,i.e, should be non-blocking Use Apache Kafka as the message queue system Follow a producer-consumer architecture with broker, Kafka Able to handle different types of notification: Group, Owner and Watching The above requirements can be satisfied if we have a package that can connect to Kafka, have an ability to handle different types of notification and is non-blocking. To handle different type of notifications we can have producers and consumers send and receive messages with different topics. So the package we need should be able to dynamically do so. Instead of having say 10 producers for 10 topics and establishing 10 connections to Kafka, it is better we have 1 producer which can send messages with 10 different topics. Similarly 1 consumer should be able to subscribe to 10 different
5 comments
Read more