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.