Application architecture for subscriber based system

Question

I am planning on building a system where users "subscribe" to "records".

Records are updated on a schedule, several times a day.

When records are updated, the user should be notified that these records have been added/updated.

I don't want each user to have their own copy of the records due to storage space and the overhead of inserting many records, one for each subscriber, each time the schedule runs.

However, each user will need to add metadata against each record they are subscribed to. Such as notes and flags that the record has been read etc.

Can anyone give me any advice or how to structure such a system or where I can learn more about this sort of application architecture?

Is this the correct place to ask?

At the moment, I'm not even sure what to Google for ;)

Answer 1

In the past, I've accomplished this with Event tables.

Events EventTypes Records Users
EventID
UserID ------------------------------------------------------------ UserID
RecordID ---------------------------------- RecordID Name
EventTypeID ---------- EventTypeID Description Title
DateTime Name DateTime Phone
Notes Description LinkToDocument

Each event record is a reckoning of something that a user did to a document, what that action was (the Event Type: Add, change, delete, print, etc.), which document the action was performed on, what date and time the action occurred, and any notes associated with the action.

• jordelver: Thanks, that makes sense. I suppose the problem is that in order to get a list of records and the events on each, we're going to have to do quite a lot of JOINs (or other data querying). I'm wondering whether it would be more simple and performant to simply de-normalize as I describe in NOT wanting to do in my original post? :) The problem then is how to efficiently create many records for say a record which has 1000 subscribers? Actually, reads are probably more important than writes as the write part will be done in a background job of some sort, whilst the reads will be an web API.