Feature proposal #6969
closed
Implement relationship table
100%
Description
This should handle who has access to the specified object. Specifically this will be used for notifications and conversations to determine who should receive the notification (probably remove the user parameter at this point) or conversation (conversations will use normal comment table). This reduces the replication. So for example instead of sending the notification to 5,000 users you'd create the notification once and it'd have 5,000 relationship entries which would be significantly faster to filter and contain less data. Example table structure as follows.
id | user_id | type | item
4 | 42 | notification | 32
Basically same as hidden table. Unsure yet if this should also apply to activity, but isn't likely necessary since activity needs to remain more dynamic. Depends a lot on the performance.
Updated by krileon over 6 years ago
This may not be necessary or provide any form of performance gain if the asset behavior of notifications can be improved for a strong PULL based method instead of using fan-out on write or read. As relationship tables basically cause a double dip into the database (normalized vs denormalized).
Updated by krileon about 6 years ago
- Status changed from Assigned to Rejected
- Target version deleted (
6.0.0) - % Done changed from 0 to 100
Query performance improved sufficiently in 4.2.0 and 4.2.1 making this no longer necessary. Global asset usage also improved in order to send notifications without replication.
Updated by krileon about 6 years ago
- Status changed from Rejected to Assigned
- Target version set to 6.0.0
- % Done changed from 100 to 0
Re-opening as this still makes sense from a storage perspective. If a notification is sent to a users connections then it could be 1000+ notifications replicated. In reality it should be 1 notification with 1000+ relationships. The relationships should be simple 2-4 column table usages as described in the original post to determine who receives the notification.
Relationships are likely only needed for notifications and conversations (to allow for group conversations). Activity and Comment streams don't need them as they don't work via replication (they work via asset).
This likely means removal of the user column for notifications. Then if all relationships are deleted we delete the notification row. This also means individual users can actually delete a notification instead of only hiding it.
Also the read state no longer needs to be a special table that's oddly notification asset specific. This can just be a part of the relationship. Example as follows.
user_id | type | item | read
Read: 42 | notification | 32 | 1
Unread: 42 | notification | 32 | 0
Note we don't need a primary id key in this case. Should load by user id, type, item and be unique index.
Allows read state to be per notification instead of blanket marketing all notifications as read by simply viewing notifications, which will be important for Conversations usage.
Updated by krileon about 3 years ago
- Status changed from Assigned to Rejected
- Target version deleted (
6.0.0) - % Done changed from 0 to 100
See #8457 for better proposal.