Click here to Skip to main content
15,608,652 members
Articles / Web Development / HTML
Article
Posted 20 Apr 2016

Stats

17.7K views
2 downloads
14 bookmarked

Improve Productivity by Redesigning DataBase Schema

Rate me:
Please Sign up or sign in to vote.
4.73/5 (5 votes)
9 Dec 2016CPOL3 min read
Redraw database structure to improve productivity
GitHub Repository
Demo

Introduction

In the IT office of the company I work for, we realize the management software with which the operational area manages its business. Our customers trust us to work files through call center. The result of these processes leads to the acquisition of information which will then be reported back to the customers.

I was immediately able to verify that the amount of information of customer interest was evolving, it was often asking us to add a binary information, that is a question that could be answered "yes" or "no" or small descriptive information entity.

In this scenario, there is the problem for this information management. Initially, it was saved in a table which collected most of the data received in the input and all the information retrieved during processing, to be returned as output. This table was very heterogeneous, there was registered information of different activities so, according to the request processing, many columns remained unused.

Another reason for the inefficiency was given by the fact that at each request to add the information, also the "flag" type, you had to add a column in the above table, with the result of having an "uncontrolled" growth of the structure and a substantial commitment to the modification of software to manage an additional column.

To solve these problems, I thought to turn the rescue of this information from a horizontal to a vertical structure. I introduced in the database a new structure through which any information requested is considered an attribute applied or not applied to the processing of a file.

Database

The database pattern consists of a table in which the attributes are defined, a table which includes the attributes by category, the types of processing and which categories of attributes are part of it. Other tables are used for the configuration for categories exclusions and exclusions between attributes, and in the end, the real processes associated to the files.

Initially, the database schema provides for a single table which has all the fields related to all kind of files:

Image 1

The database schema after the restructuring is shown below:

Image 2

Results

The structure is dynamic and highly configurable. We define the various types of operations for each operation are established the available attributes. The attributes are part of containers called "Categories". You can establish prerequisites and exclusions between categories and between attributes. For example, by selecting the attribute "Paid Invoice" of "Payment" category does not make sense to have available the attributes of the category "Debt", so then the entire category will be hidden. Another case is the exclusion of an attribute incompatible with another. For example, in the category "payment" if the attribute "Paid" is selected, then will disappear attributes "will pay" and "not will pay" and vice versa.

With this solution, to meet the request to add an information to census during processing of a file, we could set up just few tables in the system by inserting some records. The new attribute will be immediately available.

A demo can be seen at: Processing of a file

Image 3

Using the Code

The main work is done by Web User Control, on the webpage that shows the operation, and the trapdoor called by the WUC. The control works entirely in jQuery. It calls functionality exposed by the trapdoor to add or remove the selected attributes. According to the selection made, the trapdoor returns the list of valid attributes with respect to the exclusion configurations set in the system.

AttributesProcessingWUC

JavaScript
<script>
    $(document).ready(function () {
        SendRequesProcessings("Initialize", $("#hfidlManager").val(), null, null);
    });

    function Initialize() {

        var activeIndex = parseInt($("#hidAccordionIndex").val());

        $("#catalog").accordion({
            autoHeight: false,
            event: "mousedown",
            active: activeIndex,
            collapsible: true,
            activate: function (event, ui) {
                var index = $(this).accordion("option", "active");
                $("#hidAccordionIndex").val(index);
            }
        });

        $(".draggable").draggable({
            helper: "clone",
            cursor: "-webkit-grabbing",
            revert: "invalid"
        });

        //if not delay the drop of a few milliseconds before the function begins stop function of drag and eliminates the moved item with an error.
        $(".droppable").droppable({
            drop: function (event, ui) {
                window.setTimeout(function () {
                    handleDropEvent(event, ui);
                }, 10);
            }
        });

        //if not delay the drop of a few milliseconds before the function begins stop function of drag and eliminates the moved item with an error.
        $("#ProcTrash").droppable({
            accept: '.AttrSel',
            tolerance: 'touch',
            drop: function (event, ui) {
                window.setTimeout(function () {
                    handleDropOutEvent(event, ui);
                }, 10);
            }
        });

        $("#PnlAddAttribute").dialog({
            autoOpen: false,
            width: 375,
            height: 175,
            modal: true,
            resizable: false,
            closeOnEscape: false,
            open: function (event, ui) {
                $(".ui-dialog-titlebar-close").hide();
            },
            buttons: {
                Salva: function () {
                    SendRequesProcessings("AddAttribute", $("#hfidlManager").val(), $("#hfIdAttrSel").val(), $("#tbAttributeValue").val());
                    $.event.trigger('SelectedAttributeDropped', [{ idAttribute: $("#hfIdAttrSel").val(), AttributeValue: $("#tbAttributeValue").val() }]);
                    $("#tbAttributeValue").val("");
                    $(this).dialog("close");
                },
                Annulla: function () {
                    $(this).dialog("close");
                    return false;
                }
            }
        });
    }

    function handleDropEvent(event, ui) {
        $("#hfIdAttrSel").val(ui.draggable.attr('idAttribute'));
        //If the attribute has a value i open the window for entering the value and transfer send command to the window
        if (ui.draggable.attr('hasvalue') == 'true') {
            $("#ProcAttributeSel").html(ui.draggable.attr('ValueDescription'));
            $("#PnlAddAttribute").dialog("open");
        }
        else {
            SendRequesProcessings("AddAttribute", $("#hfidlManager").val(), $("#hfIdAttrSel").val(), null);
            $.event.trigger('SelectedAttributeDropped', [{ idAttribute: $("#hfIdAttrSel").val(), AttributeValue: null }]);
        }
    }

    function handleDropOutEvent(event, ui) {
        SendRequesProcessings("RemoveAttribute", $("#hfidlManager").val(), ui.draggable.attr('idAttribute'), null);
        $.event.trigger('RemovedAttributeDropOut', [{ idAttribute: ui.draggable.attr('idAttribute') }]);
    }

    function AddCategoryExcluded(idCategory) {
        SendRequesProcessings("AddCategoryExcluded", $("#hfidlManager").val(), idCategory, null);
    }

    function RemoveCategoryExcluded(idCategory) {
        SendRequesProcessings("RemoveCategoryExcluded", $("#hfidlManager").val(), idCategory, null);
    }

    function ClearCategoryExcluded() {
        SendRequesProcessings("ClearCategoryExcluded", $("#hfidlManager").val(), null, null);
    }

    function AddAttributeExcluded(idAttribute) {
        SendRequesProcessings("AddAttributeExcluded", $("#hfidlManager").val(), idAttribute, null);
    }

    function RemoveAttributeExcluded(idAttribute) {
        SendRequesProcessings("RemoveAttributeExcluded", $("#hfidlManager").val(), idAttribute, null);
    }

    function ClearAttributesExcluded() {
        SendRequesProcessings("ClearAttributesExcluded", $("#hfidlManager").val(), null, null);
    }

    function SendRequesProcessings(command, idlManager, idAttribute, AttributeValue) {
        var options = {
            error: function (msg) {
                App.alert({ type: 'danger', icon: 'warning', message: msg.d, place: 'append', closeInSeconds: 5 });
            },
            type: "POST",
            url: "/TrapDoor/FileProcessing/FileProcessingHandler.ashx",
            data: "idlManager=" + idlManager + "&command=" + command + "&idAttribute=" + idAttribute + "&AttributeValue=" + AttributeValue,
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            async: false,
            success: function (response) {
                bindAttributes(response);
            }
        };
        jQuery.ajax(options);
    }

    function f_checkEnablingFileProc() {
        return $('#hfIdAttrSel').val() != "";
    }

    function bindAttributes(jSonRes) {
        $("#hfidlManager").val(jSonRes.idlManager);
        var categories = jSonRes.ValidAttributes.AttributesCategories;
        $("#CategoriesCurtainConteiner").empty();
        $("#CategoriesCurtainConteiner").append("<div id='catalog'>");
        $.each(categories, function (idx, obj) {
            $("#catalog").append("<h2><a href='#'>" + obj.Description + "</a></h2>");
            $("#catalog").append("<div id='divCatAcc" + idx + "' style='overflow: hidden; position: initial;'>");
            $("#divCatAcc" + idx).append("<ul id='ulAttr" + idx + "' class='ulCursorClass' >");
            $.each(obj.Attributes, function (idxAttr, objAttr) {
                $("#ulAttr" + idx).append("<li class='draggable' z-index='10' id='liAttribute" + idx + idxAttr + "' idAttribute='" + objAttr.idAttribute + "' hasvalue='" + objAttr.HasValue + "' ValueDescription='" + objAttr.ValueDescription + "'>" + objAttr.Description + "</li>");
            })
        });

        var SelectedAttributes = jSonRes.AttributesProcessing.SelectedAttributes;
        $("#attrSelectedDiv").empty();
        $("#attrSelectedDiv").append("<ul id='ulAttrSel' class='droppable ulCursorClass'>");
        $.each(SelectedAttributes, function (idxAttrSel, objAttrSel) {
            $("#ulAttrSel").append("<li class='draggable AttrSel' id='liAttributeSel" + idxAttrSel + "' idAttribute='" + objAttrSel.idAttribute + "'>" + objAttrSel.Description + "</li>");
        });
        Initialize();
    }
</script>

TrapDoor

C#
public class FileProcessingHandler : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{
    FileProcessingManager lManager;

    private class jsonResult
    {
        public int idlManager { get; set; }
        public CategoriesAttributes ValidAttributes { get; set; }
        public AttributesProc AttributesProcessing { get; set; }
    }

    public void ProcessRequest(HttpContext context)
    {

        context.Response.ContentType = "application/json; charset=utf-8";
        context.Request.InputStream.Position = 0;
        NameValueCollection dataPOST = null;
        int idlManager = 0;
        int idAttribute;
        string AttributeValue;
        string command;
        bool result;
        try
        {
            using (var inputStream = new StreamReader(context.Request.InputStream))
            {
                dataPOST = HttpUtility.ParseQueryString(HttpUtility.HtmlDecode(inputStream.ReadToEnd().Replace("&quot;", "\\&quot;")));
            }

            if (dataPOST != null)
            {
                result = Int32.TryParse(dataPOST["idlManager"], out idlManager);
                command = dataPOST["command"];
                result = Int32.TryParse(dataPOST["idAttribute"], out idAttribute);
                AttributeValue = dataPOST["AttributeValue"];

                lManager = (FileProcessingManager)context.Session["lManager"];
                if (lManager == null)
                    lManager = new FileProcessingManager(-1, processTypes.DefaultedInvoice);

                switch (command)
                {
                    case "Initialize":
                        //It serves to load the Attributes may already selected in previous rounds
                        break;
                    case "AddAttribute":
                        AddAttribute(idAttribute, AttributeValue);
                        break;
                    case "RemoveAttribute":
                        RemoveAttribute(idAttribute);
                        break;
                    case "AddCategoryExcluded":
                        AddCategoryExcluded(idAttribute);
                        break;
                    case "RemoveCategoryExcluded":
                        RemoveCategoryExcluded(idAttribute);
                        break;
                    case "ClearCategoryExcluded":
                        ClearCategoryExcluded();
                        break;
                    case "AddAttributeExcluded":
                        AddAttributeExcluded(idAttribute);
                        break;
                    case "RemoveAttributeExcluded":
                        RemoveAttributeExcluded(idAttribute);
                        break;
                    case "ClearAttributesExcluded":
                        ClearAttributesExcluded();
                        break;
                    default:
                        break;
                }

                AttributesProc attributes = lManager.GetSelectedAttributes();
                if (attributes.SelectedAttributes.Count <= 0)
                {
                    //add the courtesy line
                    SelectedAttribute att = new SelectedAttribute();
                    att.idAttribute = -1;
                    att.Value = null;
                    lManager.AddProcessingAttribute(att);
                }

                context.Session["lManager"] = lManager;

                jsonResult JsonResult = new jsonResult();
                JsonResult.idlManager = idlManager;
                JsonResult.AttributesProcessing = lManager.GetSelectedAttributes();
                JsonResult.ValidAttributes = lManager.GetValidAttributes();
                context.Response.Write(JsonConvert.SerializeObject(JsonResult));
            }
        }
        catch (Exception)
        {

            throw;
        }

    }

FileProcessingManager

C#
public AttributesProc GetSelectedAttributes()
        {
            DataTable ProcessAttr = DSProcessing.Tables["AttributesProcessing"];
            DataTable Attributes = DSProcessing.Tables["Attributes"];
            DataTable Categories = DSProcessing.Tables["AttributesCategory"];

            var SelectedAttributes =
                from procAt in ProcessAttr.AsEnumerable()
                join attsel in Attributes.AsEnumerable()
                  on procAt.Field<int>("Attribute") equals attsel.Field<int>("idAttribute")
                join category in Categories.AsEnumerable() on attsel.Field<int>("Category") equals category.Field<int>("idCategory")
                select new
                {
                    idAttribute = attsel.Field<int>("idAttribute"),
                    DescriptionAttribute = attsel.Field<string>("Description"),
                    CategoryDescription = category.Field<string>("Description"),
                    HasValue = attsel.Field<bool>("HasValue"),
                    Value = procAt.Field<string>("Value")
                };

            AttributesProc result = new AttributesProc();

            foreach (var AttributeSel in SelectedAttributes)
            {
                SelectedAttribute att = new SelectedAttribute();
                att.CategoryDescription = AttributeSel.CategoryDescription;
                att.idAttribute = AttributeSel.idAttribute;
                att.Description = AttributeSel.CategoryDescription + "\\" + AttributeSel.DescriptionAttribute + (AttributeSel.HasValue ? "(" + AttributeSel.Value + ")" : null);
                att.HasValue = AttributeSel.HasValue;
                att.Value = AttributeSel.Value;
                result.SelectedAttributes.Add(att);
            }
            return result;
        }

        public CategoriesAttributes GetValidAttributes()
        {
            /* The function returns the Attributes that you have to show in the available attributes section.
             * The Attributes are filtered according to those already selected and depending on the exclusions configuration
             * 
             * Categories and Attributes are already filtered by processing type.
             * I just have to exclude categoriess and attributes are not compatible with those already selected. Added the exclusion of Attributes imposed by web.
             * 
             * the query is this:
             * select * from Attributes at
             *  where at.Category not in (select escat.CategoryExcluded from AttributesProcessing pat join Attributes attsel on pat.AttributesProcessing = attsel.idAttribute
             *     join CategoriesExclusions escat on attsel.Category = escat.Category)
             *    and at.idAttribute not in (select esat.AttributeExcluded from AttributesProcessing pat join AttributesExclusions esat on pat.idAttribute = esat.Attribute)
             *    and at.idAttribute not in (attributes list excluded from web features)
             */
            DataTable Attributes = DSProcessing.Tables["Attributes"];
            DataTable AttributesCategory = DSProcessing.Tables["AttributesCategory"];
            DataTable AttrProcessing = DSProcessing.Tables["AttributesProcessing"];
            DataTable CategoriesExclusions = DSProcessing.Tables["CategoriesExclusions"];
            DataTable AttributesExclusions = DSProcessing.Tables["AttributesExclusions"];

            var ValidAttributes =
                from attributes in Attributes.AsEnumerable()
                join category in AttributesCategory.AsEnumerable() on attributes.Field<int>("Category") equals category.Field<int>("idCategory")
                where !(from pat in AttrProcessing.AsEnumerable()
                        join attsel in Attributes.AsEnumerable()
                          on pat.Field<int>("Attribute") equals attsel.Field<int>("idAttribute")
                        join escat in CategoriesExclusions.AsEnumerable()
                          on attsel.Field<int>("Category") equals escat.Field<int>("Category")
                        select escat.Field<int>("CategoryExcluded")
                        ).Contains(attributes.Field<int>("Category")) //Exclusion of mutually exclusive categoriess
                   && !(from cat_web in this.CategoriesExclusionRequest.CategoryExcluded
                        select cat_web.idCategory
                        ).Contains(attributes.Field<int>("Category")) //exclusion of categoriess required by functionality
                   && !(from pat2 in AttrProcessing.AsEnumerable()
                        join esat in AttributesExclusions.AsEnumerable() on pat2.Field<int>("Attribute") equals esat.Field<int>("Attribute")
                        select esat.Field<int>("AttributeExcluded")
                        ).Contains(attributes.Field<int>("idAttribute")) //Exclusion of mutually exclusive attributes
                   && !(from pat3 in AttrProcessing.AsEnumerable()
                        select pat3.Field<int>("Attribute")
                        ).Contains(attributes.Field<int>("idAttribute")) //Exclusion of Attributes already selected
                   && !(from atr_web in this.AttributesExclusionRequest.AttributesExcluded
                        select atr_web.idAttribute
                        ).Contains(attributes.Field<int>("idAttribute")) //Exclusion of attributes required by functionality
                orderby category.Field<int>("Order"), attributes.Field<int>("idAttribute")
                select new
                {
                    CategoryId = attributes.Field<int>("Category"),
                    DescriptionCategory = category.Field<string>("Description"),
                    idAttribute = attributes.Field<int>("idAttribute"),
                    DescriptionAttribute = attributes.Field<string>("Description"),
                    HasValue = attributes.Field<bool>("HasValue"),
                    ValueDescription = attributes.Field<string>("ValueDescription")
                };

            CategoriesAttributes result = new CategoriesAttributes();
            int idCategoryAtt = -100;
            AttributesCategory Category = null;
            foreach (var attributesOk in ValidAttributes)
            {
                if (attributesOk.CategoryId != idCategoryAtt)
                {
                    Category = new AttributesCategory();
                    Category.idCategory = attributesOk.CategoryId;
                    Category.Description = attributesOk.DescriptionCategory;
                    result.AttributesCategories.Add(Category);
                }
                idCategoryAtt = attributesOk.CategoryId;
                Attribute att = new Attribute();
                att.idCategory = attributesOk.CategoryId;
                att.idAttribute = attributesOk.idAttribute;
                att.Description = attributesOk.DescriptionAttribute;
                att.HasValue = attributesOk.HasValue;
                att.ValueDescription = attributesOk.ValueDescription;
                Category.Attributes.Add(att);
            }
            return result;
        }
This article was originally posted at https://github.com/cervelliriccardo/LavorazionePratica

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Vitrociset
Italy Italy
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionFormat Pin
Nelek10-Dec-16 9:56
protectorNelek10-Dec-16 9:56 
AnswerRe: Format Pin
Riccardo Cervelli17-Dec-16 6:43
Riccardo Cervelli17-Dec-16 6:43 
GeneralRe: Format Pin
Nelek20-Dec-16 5:00
protectorNelek20-Dec-16 5:00 
GeneralRe: Format Pin
Riccardo Cervelli20-Dec-16 5:38
Riccardo Cervelli20-Dec-16 5:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.