mysql 表变化 通知,设计通知数据库表

I develop a notification table in MySQL whom attributes are

1.Created_At DateTime using current timestamp

2.notification_type varchar(100)

3.IS_Read boolean

4.message which I've to show according to the notification type

5.Notification_ID auto_Increment as a primary key

These are the attributes which I enter in my notification table

Now my questions is that Are these attributes are enough ?? or should I have to add more ??? What attributes should be in the notification table ??

Edited

Ok now my notification table attributes look like

1.Created_At DateTime using current timestamp

2.notification_type varchar(100)

3.Status int(2) in this I'll store notification status that it is shown and is read or not in binary format [ 00 , 01 , 10 ,11 ]=left side of binary reperesentation indicates whether the notification has been read and the right side is whether it's hidden.

4.message which I've to show according to the notification type

5.Notification_ID auto_Increment as a primary key

6.User_ID as a foreign key from table users. whenever some deletion insertion or updation is applied the notification generate and send it to the relevant user from this User_ID.

Now again my question is same

Are these attributes are enough ?? or should I have to add more ??? What attributes should be in the notification table ??

解决方案

Your question is too generic, and it is very hard to answer it.

But I will still try to answer them, in a more conceptual manner:

You should always add a primary key to your tables, especially to this one, as you are going to update the row, in order to set the is_read field. In this case you need some kind of ID, you can call it id, this ID will represent a notification ID, and it should be an auto-increament.

Also, I would anter a status field instead of is_read to indicate the several states possibilities for a single notification.

Also, I think your table is missing a destination object, for example the user that this notification is related to. In case of general notifications (to all users), you do not ned this.

Basically, if you want to show it inside your website, you should create a script (PHP script in your case) that receives all unread notifications (or all of a specific user) from the DB and returns a list of notification messages, a long with their IDs, in a JSON or any other format you like. Then the client (JavaScript for example in your website) should call this script in order to get the list of notification to show. You can setup a script to check every several minutes to search for any new notifications (or use a socket connection, but it's more complicate as a POC, especially for someone who new to this).

I hope this explains the basic concept for how it should works, and where you should start.

If you have a more specific or technical questions, I would be happy to assist.

Good Luck!

now could you explain more about status variable

Status is represented as INT and it's yours to decide. For example, if you want to hide your notification from the notification panel, then it should be indicated by status. Let's say you have two different state types, hidden or not, and read or not. In this case it is so classic to create the status in the following binary representation: 11, 10, 01, 00. When the left side of the binary representation indicates whether the notification has been read and the right side is whether it's hidden.

For example:

Status 0, represents 00 in binary, means that the notification is shown, and not read.

Status 1, represents 01 in binary, means that the notification is hidden, and not read.

Status 2, represents 10 in binary, means that the notification is shown, and it has been read.

Status 3, represents 11 in binary, means that the notification is hidden, and it has been read.

Later you can easily check with binary operators, for each case, for example in JavaScript:

var STATUS_READ = 2;

var STATUS_SHOW = 1;

function isRead(status)

{

return !!(status & STATUS_READ);

}

function isVisible(status)

{

return !!(status & STATUS_SHOW);

}

also please elaborate the destination object which you are talking about, that how it works and when to use and why. Also tell me about the destination object datatype

Let's say you have users in your system. Each user have its own user_id.

Let's say you wnat to show a notification per user, therefor, you should also attach the notification destination user in your notifications tables.

In this case you should add the column user_id which holds the ID of the user, usually it is represented as INT, but it can also be a username wich is VARCHAR, it depends how did you designed your user table.