I divide intra-site messages into all user messages and some user messages and a specific user message. For "all user messages", "some user messages", and "a specific user message", how can we record whether a user has read this message. How should I design the data table structure? Consider for... I divide intra-site messages into: all user messages and some user messages, a specific user message.
For "all user messages", "some user messages", and "a specific user message", how can we record whether a user has read this message.
How should I design the data table structure?
Consider not displaying "all user messages" or "some user messages" sent to new users ".
Reply content:
I divide intra-site messages into all user messages and some user messages and a specific user message.
For "all user messages", "some user messages", and "a specific user message", how can we record whether a user has read this message.
How should I design the data table structure?
Consider not displaying "all user messages" or "some user messages" sent to new users ".
Data Structure Decision Algorithm. If you are confused at any time, analyze the data itself.
- MessageAndUserYesEntityThe message is sent as a connection object.Link.
- All/part/single messages can be usedMany-to-many.
Then the table structure is designed according to the database's paradigm rules:
- Msg: ID,... (other random, such as creation_date, text, sender_ID, etc)
- Msg_deliver: ID, msg_ID, usr_ID, unread (default = true)
- Usr: ID,... (other random)
- Msg_deliver table, there should be no records with duplicate msg_ID and usr_ID (the index constraints can be considered)
The following actions are required to add a message:
- In any case, the message body is created in the msg table.
- Add an entry to msg_deliver for each receiver.
- Add as many records as the usr table for the whole, add some records for the part, and add one record for one-to-one operation.
Other operations:
- Query unread messages. query usr_ID and unread = true in the msg_deliver table. To obtain message titles and other information at the same time, use the Combined Query to pull the msg table in.
- When reading a message, query the msg table again to obtain the message body.
- Mark as read, query usr_ID and msg_ID, and then change unread to false
- Read all, query usr_ID, and change the unread value of all records to false.
- For new users, there is naturally noLinkTo connect old messages, the requirement will be solved naturally
Other suggestions:
- In the intermediate table msg_deliver, You Can cache the message title and sender nickname as needed to reduce the number of queries.
- If a large amount of concurrent data is involved, you can consider Table sharding. This table sharding is generally performed with the user table sharding.
- If the data has accumulated too much, you can archive the data and use the resources as much as possible to quickly query recent data. Because the older the message, the fewer people are queried, the fewer resources are used.