类似于 Facebook 的通知系统的数据库架构 [英] Database Schema For Notification System Similar to Facebooks

问题描述

我正在尝试设计一个类似于 facebook 的通知系统，但我遇到了一些困难.我的要求是能够支持无限数量的通知类型，这些类型可能需要呈现不同类型的元数据.

I am trying to design a notification system similar to facebook and I have reached a bit of a brick wall. My requirement is to be able to support an infinite number of notification types that may have different types of meta data required to be rendered.

我想我会设计如下架构:

I'm thinking that I will design the schema as follows:

**Notification**
Id (int)
TypeId (int)
RecipientId (int)
SenderId (int)
SendDateTime (DateTime)
Read (bool)
MessageData (...Blob?)
Deleted (bool)

**NotificationType**
Id
Name
Description

我真的想尽量避免在我的数据库中存储 HTML 字符串，但是，我也不是特别喜欢存储 blob.

I really want to try to avoid storing HTML strings in my database, however, I also am not particularly fond of storing blobs either.

我可以查看 NotificationType 表并引用另一个存储特定于该类型的数据的表，但是，这意味着每次我创建一个新的通知类型时，我都需要创建一个新表.我相信我也会让自己陷入一个不得不编写动态 SQL 来获取数据的世界.

It is possible that I could do a look up on the NotificationType table and reference another table that stores data specific to that type, however, that would mean that everytime that I created an new notificationtype i would need to create a new table. I believe that I would also be getting myself into a world of having to write dynamic SQL to get the data out.

有人对我有什么建议吗?

Does anyone have any suggestions for me?

推荐答案

这是我最终解决这个问题的方法.

Here is how I ended up solving this problem.

我决定使用字典来存储每种通知类型独有的数据.然后我将该字典对象序列化为一个二进制字符串，并将其与每个通知一起存储在数据库中.我有一个模板分配给每个包含占位符的通知类型，即.'{song-title}'，我可以用字典对象中的值快速替换它.

I decided to use a dictionary to store data that is unique to each notification type. I then serialize that dictionary object into a binary string and store that in the database along with each notification. I have a template assigned to each notification type that contains place-holders ie. '{song-title}' that I can quickly replace with values from my dictionary object.

这篇关于类似于 Facebook 的通知系统的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！