The decision table for discount process

Overview

In the context of Point of sales (POS) for many industry, it is quite common that a shop might run promotion on different period, weekday and whether holiday should give discount or not. The discount process that we are discussing in this article is an automated process. Meaning that the cashier will scan the barcode of the item and the discount will be given automatically.

To run a complex price discount process based on the date factor, the decision table will look like this:

Rule #	Is holiday	Day of week	Exclude holiday	Include holidy	Give discount?
1	n	y	n	n	y
2	n	n	n	n	n
3	y	y	n	n	y
4	y	y	y	n	n
5	y	y	n	y	y
6	y	n	n	n	n
7	y	n	y	n	n
8	y	n	n	y	y

The decision table explanations

• 'Is holiday' is a table that stores the date of holiday. This table is to be setup by the system admin.
• 'Day of week', 'exclude holiday' and 'include holiday' is a boolean (checkbox field on the scren) and the setting should be kept in the 'price discount' table. This table is to be setup by the system admin as well.
• 'Day of week' which has 7 fields that includes Monday, Tuesday, etc. 'y', means that week day has discount.
• 'Exclude holiday' is a field that is used in conjunction with 'day of week'.
• 'Include holiday' is a field that is used in conjunction with 'day of week' and it is mutually exclusive from 'exclude holiday'.

Use case

• Discount is applicable for weekdays (Monday to Friday) regardless holiday - rule #1 and #3 applies.
• Discount is applicable for weekdays (Monday to Friday) excluding holiday - rule #4 applies.
• Discount is applicable for weekdays (Monday to Friday) and applicable holiday - rule #5 applies.
• Discount is not applicable for weekdays (Monday to Friday) regardless holiday- rule #2 and #6 applies.
• Discount is not applicable for weekdays (Monday to Friday) and applicable on holiday - rule #8 applies.
• Rule #7 is invalid. So, we may ingore it.

Table structure

We need three tables to store the master setup. One for the holiday, one for the product and another one for the discount. It's a one to many relationship between tb_prod and tb_discount.

create table tb_holiday (
    dt datetime not null
);

create table tb_prod (
    prod_id int not null
    , price money not null
);

create table tb_discount (
    prod_id int not null        
    , start_dt datetime null
    , end_dt datetime null
    , discount_pct numeric(6,2) null
    , for_mon int null
    , for_tue int null
    , for_wed int null
    , for_thu int null
    , for_fri int null
    , for_sat int null
    , for_sun int null
    , exclude_holiday int null
    , include_holiday int null
)

Setting up the test data

delete from tb_holiday;
delete from tb_prod;
delete from tb_discount;

insert into tb_holiday (dt) values 
('2021-01-13');

insert into tb_prod (prod_id, price) values
(1, 100)
, (2, 200);

insert into tb_discount (prod_id, start_dt, end_dt, discount_pct
, for_mon, for_tue, for_wed, for_thu, for_fri, for_sat, for_sun
, exclude_holiday, include_holiday) values 
(1, '2021-01-01', '2021-01-31', 0.1
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-02-01', '2021-02-28', 0.15
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-01-01', '2021-01-31', 0.05
    , 0, 0, 0, 0, 0, 1, 1
    , 0, 1)
, (1, '2021-01-01', '2021-01-31', 0.03
	, 0, 0, 0, 0, 0, 1, 1
	, 0, 1);    

To query the discount for any product

declare
    @is_holiday int 
    , @dow int 
    , @curr_dt datetime 
    , @prod_id int

set @prod_id = 1
set @curr_dt = '2021-01-11'
set @dow = datepart(weekday, @curr_dt)

if exists(
    select *	
    from tb_holiday
    where dt = @curr_dt
)
begin
    set @is_holiday = 1
end
else
begin
    set @is_holiday = 0
end

select 
    discount_pct
from tb_discount d
where
    prod_id = @prod_id
    and @curr_dt between d.start_dt and d.end_dt
    and (			
        (@is_holiday = 1 and (d.include_holiday = 1 and d.exclude_holiday = 0))
        or ((
                   (@dow = 1 and d.for_sun = 1)
                or (@dow = 2 and d.for_mon = 1)
                or (@dow = 3 and d.for_tue = 1)
                or (@dow = 4 and d.for_wed = 1)
                or (@dow = 5 and d.for_thu = 1)
                or (@dow = 6 and d.for_fri = 1)
                or (@dow = 7 and d.for_sat = 1)
            ) 
            and (
                (@is_holiday = 0)
                or (@is_holiday = 1 
                    and (d.include_holiday = 1 or d.exclude_holiday = 0)
                )
        ))
    )

• The above query first is to identify the day of week and check to see if it is holiday.

• Follow by filtering the discount record by prod ID and the period.

• It then checks the day of week and holiday.

• Finally, returns the discount percentage.

• Test case (please change the value of @curr_dt in the query)

  • For @curr_dt = 2021-01-11 (Monday), the discount percentage is 10%. The week day discount in Jan 2021.
  • For @curr_dt = 2021-01-13 (Wednesday, holiday), the discount percentage is 3%.
  • For @curr_dt = 2021-01-16 (Saturday), the discount percentage is 5%. There is a caveat in the result because the query returns two discount records.
  • For @curr_dt = 2021-02-11 (Thursday), the discount percentage is 15%. The week day discount in Feb 2021.

• Caveat - there might be more than one discount record returned by the query. To avoid this from happening,

  1. During the discount setup time, you may have to prompt the user on the conflict OR
  2. In case the conflicting discount allows to be saved, you may have to return the latest discount record using TOP 1 + ORDER BY modified_on or any other condition that will force the query returning only one discount record.

Loop all records

Basically, there is another way of looping which uses CURSOR. But, it requires record locking which may not work as fast as you think.

In this article, we are going to use a temporary table to keep the data and then loop it with "seq" filter. The following example which is printing all table names on the screen. You may modify the PRINT line so that it compose the necessary SQL statement before you print it or execute any dynamic SQL.



set nocount on

declare
    @seq int
    , @name nvarchar(255)

-- declare a temporary table in the memory.
declare @tb table (
    seq int identity(1,1)
    , name nvarchar(255)
)

--copy all the necessary records to the temporary table '@tb'
insert into @tb (name)
select name
from sys.objects
where
    type = 'u'and
    name like 'tb_suggest%'
order by name

-- init the var before use
set @seq = 0


-- start the lopp
while exists(
    select *
    from @tb
    where seq > @seq
)
begin

    -- always load the first record that is greater than
    -- last seq.
    select top 1                --<<=== this is very important!!!
        @seq = seq
        , @name = name
    from @tb
    where seq > @seq
    order by seq                --<<=== this is very important!!!


    -- do whatevery necessary process here.
    -- you may use 'set xxx' to join the sql string before print it out.

    print '@name => ' + @name

end

How to find out which index is missing

To find out the missing index, we have to analyze the information in the following system views:

• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_details

You may download the script that combine all the necessary information from the following URL:

   https://gist.github.com/alexsorokoletov/a079629f9e1435c7f81f

And here is the SQL script:

SELECT
    CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle, mid.index_handle,
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
        + ' ON ' + mid.statement
        + ' (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
        + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

    migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
--and database_id =  DB_ID('my_database')

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


How to use it?

• I'm relying on improvement_measure value and I review the top 5 missing index information and then decide if I should create an index. 
• We should not create all the indexes returned by this query. Because some of the missing indexes can be merge into one index.
• We should review the existing indexes and compare against what is missing and then decide the new index. This might involves deleting the existing index before creating a new one.

Life is tough with naming convention especially we want to know how many times that we have reviewed a particular index. My naming rule works this way:

• IX_my_table_1 - this is the first index.
• IX_my_table_2 - this is another index.
• IX_my_table_2_1 - this is the newer version of index where IX_my_table_2 has been dropped and merge with the new missing columns.

Running the same stored procedure sequentially

Use the following system stored procedure to block the same stored procedure from running concurrently.

   sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

   sp_releaseapplock

Note:

• The above system stored procedures must be running within a database transaction.
• If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called. 
• If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.

Lock or no lock

While I was reviewing other programmer's work, I found out that most of the SELECT statements contain this keyword - "WITH (NOLOCK)". So, I posted this question to the programmer who is handling the job. His answer is quite simple, "we need this to reduce the query response time and then our program will run faster". And the actual fact is that, the programmer does not know the side effect of this keyword.

Yes, the query will run faster because it does not require to lock all the necessary records before reading it from the table. But, the side effects is that the data returned by the query might be "dirty" (i.e., the changes have not been committed).

If the program has read the data which have been rolled back by other user, it could lead to many unpredictable situation. This could lead to unsolvable mystery.

Using dynamic SQL for reporting process

What is the user requirements for the report?

• Lots of criteria on the screen but most of them are optional.
• The final result is a subset of a result.
• User allows to choose different sorting order.
• User allows to choose different grouping.
• The fields in the report was specified by the user at runtime.

By using dynamic SQL, you will be able to compose the appropriate SQL statement and then meets the user expectation. Another thing is that, you don't need to have many static SQL statements in the stored procedure.

Dynamic SQL usage

One of the usefulness about dynamic SQL is for database maintenance. For example, you want to run the maintenance process in the selected databases.

Below is the flow of using dynamic SQL to ease the maintenance:

• Create a table that stores the database names for your system.
• Declare a cursor and loop through all your database names and match the process number (explain later).
• Generate the dynamic SQL statement to be executed against the selected databases.
• Finally, execute the dynamic SQL.

The reason to have a table to stores your database names:

• You are not interested in all databases stored in sys.databases. You just want to run the script against the selected databases.
• The database name did not follow any convention before you take over the maintenance. In you are handling the database server, then, you should follow a standard naming convention for the database name. This allows you to query the database name from sys.databases.
• You don't want to execute the SQL statement against all your application databases. Only certain databases are going to run script 1 and other databases are going to run script 2, etc.

The table design to stores the database name:

• db_name nvarchar(255) - this is the database name
• process_1 int - if '1' means run the maintenance stored procedure #1. '0' to ignore.
• process_2 int - if '1' means run the maintenance stored procedure #2.'0' to ignore.
• (other process flag).

Executing dynamic SQL

In MSSQL, it is able to execute dynamic SQL. Meaning, you are allowed to execute the SQL statement that stores in a variable by calling sp_executesql system stored procedure. It compiles the SQL statement at runtime and then execute it. You may also create parameterized SQL statement so that it compiles once and run multiple times (for example, inserting many records within a loop).

Whenever it's possible, try to avoid using the dynamic SQL. This is because each time you are executing the dynamic SQL statement (not within a loop in the stored procedure), it will be recompiled and increase the execution time.

The advantage of implementing this idea will save you from having a permanent table to store the reporting data  or data for the current session. Also, it saves you having tuning the SQL statement with complex WHERE clause where some of the conditions were optional.

The following sample code returns all the object ID-s in the database by using the dynamic SQL statement.

declare @sql nvarchar(max)

select @sql = '
    declare @tb table ( id int )

    insert into @tb (id)
    select object_id from sys.objects

    select * from @tb
'
exec sp_executesql @sql

I will discuss a bit more in detail in my next article.

Generating "reminder" for the user

In a mid to large scale application, often you need to run scheduled tasks which can be schedule through the SQL Agent. One type of the schedule is to generate reminder or alert for the user. For example, inform the collection department about the overdue invoice.

Below is the strategy to implement this type of process:

1. Create a stored procedures to scan the data.
2. In this stored procedure, it contains the business logic about what data will meet the requirement and then generate the reminder record. 
3. The reminder record will be saved into tb_mail (this table stores the email to be pickup by a C# program).
4. Develop a program in the C# application which will send all the emails in tb_mail table.

In case you don't want to notify the user through email, you may append this reminder record into an "alert" table (which can be "tb_log" table in our design). Then, display the alerts upon user login.

For those alert requires immediate attention, you may send the reminder or alert through SMS (short messaging).

Benefits of using stored procedure instead of writing codes in C#

Started from my favorite programming language, Visual Basic 6 (that was in 1997), I've been thinking where is the best place to implement the "business rule". After many years of moving the business rules from the the programming language to the database and vice versa, I've finally decided the best place for the business rule is in the stored procedure.

The simplest stored procedure is for SELECT, INSERT, UPDATE and DELETE. Indeed, you can do more than that. Benefits of using the stored procedure:

• Create audit log when doing any of the CRUD operation.
• Able to implement sophisticated control in what kind of data can be return to caller.
• Easier to enhance/process the records before returning the records.
• Stored procedure has been compiled and it runs faster than the ad-hoc query.
• Able to enhance the stored procedure at anytime.
• You don't have to recompile the application if there are any changes in the rule.
• You don't have to redistribute the application (at client workstation or web server) if there are any changes in the rule.

Well, I know that you might not 100% agree with my argument. But, as a customize solution builder like me, we can't ask all the users to stop for a while and wait for the latest update.

Reading the system settings using stored procedure/function

Stop hard-coding the constants in a stored procedure. Instead, you should read the value from your "settings" table or returning the constant value by a function call. In C# and other programming languages, you can declare constant in a class and use it anywhere in the program. But, in MSSQL, you can't do that.

To get the constant or settings, you may try to do this:

• Create a function which returns the value (could be constant or read a field from a table).
• Create a stored procedure and returns the value through OUTPUT parameter.

There are some factors which you might have to decide how to design the stored procedure/function:

• Should the value configurable by the the user? If no, you may return the value by a function call. If the value is user configurable, then, you need a "setting table" to store the value.
• If the value must be stored in a table, do you store the value in a generic table structure (with prop_name and prop_value fields) OR do you need to design a "settings" table which contains only 1 record (with the appropriate field name which can reflect the setting)?

In our database design, we have a generic table that store setting. In case the values are frequently use, we will design a setting table to store the user configured values.

Show me the runtime value

In MSSQL, you may use PRINT statement to display some information when you are executing stored procedure or a SQL batch. This is very helpful to debug the stored procedure which contains many IF statements or you would like to find out the runtime value of certain variable.

For example,

declare @cust_name nvarchar(255)

select @cust_name = cust_name
from tb_customer
where cust_code = 'A001'

print @cust_name

When to use it:

• Your stored procedure contains many IF statement and you want to know which path has been executed with different parameter value.
• You want to find out which stored procedure has been called when the main stored procedure has been executed.
• You want to find out what is the runtime value of the variables.
• You want to find out where is the process has exited.

Enabled debugging mode within the stored procedure without using parameter

In our previous article, we discussed that the stored procedure should contain a parameter call "@debug_mode" so that it can be debug in the development phase. But, some people might feel that it makes the stored procedure looks ugly. In this article, we are going to show you another way to enable the debugging in the development without having this parameter.

To store the information to the current session/connection, use the following command:

   SET CONTEXT_INFO 0x1000

To retrieve the value, use the following function:

   SELECT context_info()

context_info able to store upto 128 bytes of information. The only thing is that you must learn how to work with bitwise operator.

First thing you have to do with context_info is to define the meaning of each "box". For example, you need to implement 4 hidden features. Then, the value will look like "0x0000" where "0x" indicates it is hex value and you can't use these two character to store any information. But, the 4 digits after "0x", you are freely to define the meaning.

• 1st box - if "1" means enable the debugging mode. Otherwise, "0".
• 2nd box - could be defined as "1" to log down the process details.
• 3rd box - could be defined as "1" to print the runtime value in the IDE.
• 4th box - could be defined as "1" to rollback the process so that it won't disturb the test data.

Lastly, before you run your stored procedure, you have to call "set context_info 0x1001" to enable the debug mode and rollback the changes.

Some modification is required in your stored procedure:

declare
  @info varbinary(128),
  @debug_mode int,
  @rollback_data int

select @info = context_info()

if @info & 0x1000 = 0x1000
  select @debug_mode = 1
else
  select @debug_mode = 0

if @info & 0x0001 = 0x0001
  select @rollback_data = 1
else
  select @rollback_data = 0

(..contine your process here..)

References:
http://msdn.microsoft.com/en-us/library/ms187768.aspx
http://msdn.microsoft.com/en-us/library/ms176122.aspx

Allow to run the process again?

For the stored procedure that is executed by the SQL Agent (i.e., the scheduler), you might have to prevent the same stored procedure run more than once.

Allow re-entrant

Not all processes allow to run more than once. For example, the stored procedure that is responsible for generating monthly statement. Another example is the stored procedure that is generating the exception report.

Why re-entrant
  
In the development phase, you might want to run the stored procedure as many time as possible so that you can fine tune and debug the process. But, once these stored procedures go live, it will run once for the day, week, month or whatever predefined date.

Now, how to prevent re-entrant
In order to prevent the stored procedure to run more than once for a day, you need to have a condition to check the log table. In our example, the "tb_whatever_process_log" table is keeping track the process date.

    if exists(
        select *
        from tb_whatever_process_log
        where
            process_date = cast(convert(nvarchar, getdate(), 102) as datetime)
    )
    begin
        print 'Already done. Exit'

        set nocount off
        return
    end

    (... continue with the actual process..)

How to test this stored procedure?

Due to the re-entrant prevention condition, you will find it almost like no way to test out the stored procedure during the development phase. An easiest way to do this is to add a new parameter to the stored procedure call "@debug_mode int = 0". In the development phase, you will have to specify the "@debug_mode = 1".

How many lines should be written in a stored procedure?

I have an experience while participating in a project. While reviewing the stored procedure done by the programmer, I was having problem in understanding the stored procedure not because of it is complexity but it's length. One stored procedure consists of a few thousand lines of code. I was scratching my head and asked myself is it necessary to write everything in one stored procedure? No. It should be break into many stored procedures and then call by the main stored procedure.

Our stored procedure development guideline is quite simple and straight forward where it must:

• Must be able to re-test and/or re-use.
• Must be written in a maintainable way - if the stored procedure is handling small process, then, it should be easier to maintain.
• The stored procedure should contains less than 1,000 lines. I mean, that should be the maximum it should be.

How should we name the stored procedure?

As the project goes bigger and bigger, many enhancements were done to the existing systems, you might find out that the programmer must have discipline in naming the stored procedure and other database objects. Otherwise, the system maintenance is going to be a painful job.

Let me explain a bit on the stored procedure naming convention:

  "pr_" + "module initial" + "sub module initial" + "process name"

• The stored procedure name starts with "pr_" instead of "sp_" where "sp_" is meant for MSSQL system stored procedure. Basically, "pr_" stands for "procedure".
• "module initial" is the second section in the stored procedure name where it is short code or initial of the module name.
• "sub module" is the third section in the stored procedure name and it denotes the short code or initial for the sub-process.
• Lastly, "process name" is the actual process to be implemented within this stored procedure.

For example,

• pr_pos_rpt_sales - the module name is "pos" (i.e., Point Of Sales) and sub module name is "rpt" (i.e., Report). The "sales" means that it is sales data related process. From the stored procedure name, it is intuitive and it simply means that this stored procedure is handling the POS sales report generation.
• pr_ic_process_trans - the module name is "ic" (i.e., Inventory Control), the sub module name is "process" (i.e., process related) and "trans" is the short name for "transaction". This stored procedure is responsible for processing the inventory transactions.

For other database objects,

• "tb_" is for the table object.
• "fn_" is the function.
• "vw_" is the view.

How to design the reporting process that involves in multiple sub-processes

One problem that you might be facing (if you are doing the above mentioned) is that the main stored procedure is calling multiple sub-processes (i.e., other stored procedures) and generate/process some data and returns it to the main process. How do you simplify the entire process?

You need the following tables in order to split the entire process into smaller task which eases you from the testing to the maintenance:

• A table for storing the user parameter values - in this  table, it stores the user parameter values in a row. Each row is meant for one process session. By having this table, you will have to pass the process session ID to the sub-process and the sub-process will be able to load the parameter value from that is required. Another advantage of having this table is that you will be able to tell the actual parameter value that has been passed in. In case, the auditor would like to view what and who has been queried, you may retain the record in this table.
• A few report working tables - all the data that has been processed will be stored in this table for other module to pickup. For example, the report writer will query this table for the final output. The reason why you need many working tables is that it allows you to query the necessary data and stores into first table. Then, run an aggregate process and store it into another table as the final output. This will be very useful when it's not straight forward for you to get the final output by using one SELECT statement.

The stored procedure design:

• A stored procedure that is responsible for saving the parameter into the parameter table.
• All stored procedures must have "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Note: please take note that the above design is relying on "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Real life example:

• Generating Balance Sheet report (i.e., an Accounting report) which consist of multiple sections that includes at least the fixed assets, current asset, current liabilities and capital. The raw data might be stored in different table and requires some aggregate over the historical data.

Passing the report parameters into stored procedure

When you are designing a screen which allows the user to choose the period, multiple customer types, etc, you might have a hard time in passing the values to the stored procedure. The common way to achieve this by having a nvarchar(max) parameter type to cater for the multiple customer types. The simplest way to handle multiple values passing by the caller is splitting the incoming string into table variable.

For example, the user want to get the sales report for the selected customer types (corporate customers, travel agents and walk-in customers) for year 2011. Then, you might have something similar as shown below:

exec pr_rpt_sales_by_cust_type
  @start_date = '2011-01-01',
  @end_date = '2011-12-31',
  @customer_type = 'corporate, travel agent, walkin customer'

Assuming that we are using the display text to filter the data to ease the explanation. In real live, you might have to pass in the foreign key value.

Also, you need another function or stored procedure to parse the @customer_type parameter into table variable which you might have to do some research. ;)

Ok. Back to this topic. The select statement will be something like this:

select customer_type, sum(sales_amount) as total_sales_amount
from vw_sales
where transaction_date between @start_date and @end_date
  and (customer_type in (
      select customer_type
      from dbo.fn_parse_param_to_table(@customer_type))
    or @customer_type = ''
  )
group by customer_type

Note: "fn_parse_param_to_table" is the function that you have to develop it.

You need an application log for your systems

All systems requires a common area to keep track certain information. For example,

• The exception/error which was not handled properly - this eases the system maintenance.
• The user login and logout - you may use this log to keep track all security related activities. For example, append a "warning" log when someone is trying to hack the system especially the web base system.
• When certain system settings were missing and the system continues to work using default value - this is very important if you are distributing your system to more than one site. For example, the system administrator forgot to set the new sales email address.
• When user clicks on certain function which handles using stored procedure in the database - this is important because the user might already have clicked it multiple times and the system like no-effect. By creating log record into this table, the system administrator will be able to tell whether the stored procedure is running or not and carry out necessary recovery.

Table design:

CREATE TABLE tb_log (
    log_id uniqueidentifier NOT NULL,        --primary key
    log_type_id INT NOT NULL,                --type of log.
    workstation NVARCHAR(255) NOT NULL,        --the workstation name
    uid NVARCHAR(255) NOT NULL,                --the current user ID.
    msg NVARCHAR(max) NULL,                    --the details of the log
    remarks NVARCHAR(255) NULL,                --the remarks/procedure name.
    is_sent INT NOT NULL,          --if '1', means the log has been email to admin.
    app_id INT NOT NULL,                    --the application ID
    module_id INT NOT NULL,                    --the module ID
    modified_on DATETIME NOT NULL,            --the last updated date/time
    modified_by NVARCHAR(255) NOT NULL,    --the person who perform last update.
    created_on DATETIME NOT NULL,     --this is the current date/time when the log record is inserted.
    created_by NVARCHAR(255) NOT NULL        --the current user ID while creating this record.
);

The allowed values for the log_type_id field:

1. Error - this indicates that the system crashed at runtime and the value in the msg field is the exception details.
2. Information - this indicates that some processes have been kicked off and running smoothly.
3. Warning - this indicates that some certain system settings were missing or using default value but the system is still able to function normally.
4. Audit Log - this indicates that the log is security related or certain important data has been changed by the user.
5. Critical - this indicates the system crash is critical or someone is trying to figuring out the login ID or password and requires immediate attention.

Add-on module to be build on top of this log table:

• Email the log records to the system administrator (and mark "is_sent" with value of "1"). The email can be send in daily or weekly basis.
• If the log_type_id indicates that it is a "threat" or "hacking", the log record must be send immediately to the system administrator.

Note: for process logging, please refers to "Tracking the progress of a long run process" article.

Designing a custom mail process

Another important feature in every system is the email notification. The email notification is useful for the following situations:

• Workflow such as the application that requires approval;
• Notifying the system administrator on the current system status (such as server health report);
• Sending daily or weekly reports to the user;
• An error has happened;
• System crash report;
• etc.

The email notification does not limit to the above. You may apply this concept into the process logging feature as mentioned in the earlier blog or any other situation that requires user's attention.

Below is the table to store the email record. But, we are not using the MSSQL email notification feature to send the email out. You have to rely on external program to load the email contents and send it out. This gives you a choice of implementing this idea in any database engine.

CREATE TABLE tb_mail (
    mail_id UNIQUEIDENTIFIER NOT NULL primary key, -- stores the value of NEWID().
    send_to_email NVARCHAR(MAX) NULL,  -- stores the recipient email address.
    cc_to_email NVARCHAR(MAX) NULL,  -- stores the CC of the current email.
    subject NVARCHAR(255) NULL,  -- stores the email subject.
    body_text NVARCHAR(MAX) NULL,  -- stores the email message.
    attach_file NVARCHAR(MAX) NULL,  -- stores the attachment file name.
    created_on DATETIME NULL,  -- the record creation date/time.
    sent_status_id INT NULL,  -- the email sending status. '0'-pending, '1'-sent & '2'-failed to send
    sent_on DATETIME NULL,  -- the date/time of sending the email.
    mail_type_id INT NULL  -- the email type (optional)
);

In the module that is responsible for sending the email, you must implement the following feature:

• Able to send all the emails (sent_status_id = 0) out and
• Able to send a specific email out upon request (which could be trigger through a TCP port or WCF).