Actions log table structure

Question

Hi,

I have to write a service which will implement some kind of a black/white list manager. Short description :

It is very similar to messaging in social networks. imagine by default anyone can send you a message. And you have the possibility to block it for everyone, so no one can send you a message. Once it is blocked you can have a "white list" of contacts who can send you messages. Also you can have black list of contacts for whom is not allowed to send you any messages while service is unblocked. So shortly this is the list of actions allowed for customer :

• Block/Unblock Service
• Add/Remove/Replace from Black/White list

So the question is this : How you would structure the tables in DB for keeping list of customers who have blocked the service, black and white listed numbers and what is important for me in this stage, how you will structure the table of actions done by customer. Like to keep a history for each action (Block/Unblock..Add/Del/Replace)

Thank you.

Answer 1

bw_list table columns:

user_id - user ID of blocked/whitelisted user
list_type - either 'black' or 'white' list, although I would suggest using an integer of 0 or 1

actions_log table columns:

user_id - user ID
action_type - type of the action, for easier tracking when searching through the logs
details - free text describing the action
timestamp - date and time at which the action has occured