数据建模架构jQuery-UIVisual Studio 2013WebFormsSQL Server 2008LINQ架构师.NET4AjaxjQueryHTML中级SQL ServerSQLJavascript.NETASP.NETC#
通过重新设计数据库架构提高生产力
4.73/5 (5投票s)
重绘数据库结构以提高生产力
GitHub 存储库
演示
引言
在我工作的公司 IT 部门，我们负责开发管理软件，以便运营部门管理其业务。我们的客户信任我们通过呼叫中心处理文件。这些流程的结果是获取信息，然后将信息反馈给客户。
我立刻能够验证，客户感兴趣的信息量正在不断增长，我们经常需要添加一个二元信息，即一个可以回答“是”或“否”的问题，或者一些小的描述性信息实体。
在这种情况下，就出现了信息管理的问题。起初，它被保存在一个表中，该表收集了输入时接收到的大部分数据，以及处理过程中检索到的所有用于输出的信息。这个表非常异构，记录了不同活动的信息，因此，根据请求的处理，许多列都未使用。
效率低下的另一个原因在于，每次请求添加信息（即使是“flag”类型）时，都必须在上表中添加一个新列，结果是该结构“不受控制”地增长，并且需要大量修改软件来管理额外的列。
为了解决这些问题，我考虑将这些信息的保存方式从水平结构转变为垂直结构。我在数据库中引入了一个新的结构，通过该结构，任何请求的信息都被视为一个应用于或不应用于文件处理的属性。
数据库
数据库模式由一个定义属性的表组成，一个包含按类别划分的属性的表，处理类型以及哪些属性类别属于它。其他表用于配置类别排除和属性之间的排除，最后是与文件相关的实际处理。
最初，数据库模式提供了一个单一的表，其中包含与所有类型文件相关的字段。
重构后的数据库模式如下所示：
结果
该结构是动态且高度可配置的。我们为每种操作定义不同的操作类型，并建立可用的属性。属性属于称为“Categories”的容器。您可以建立类别之间以及属性之间的先决条件和排除。例如，选择“Payment”类别下的“Paid Invoice”属性后，就没有必要再显示“Debt”类别下的属性了，那么整个类别都会被隐藏。另一种情况是排除与另一个属性不兼容的属性。例如，在“payment”类别中，如果选择了“Paid”属性，那么“will pay”和“not will pay”属性将会消失，反之亦然。
通过这种解决方案，为了满足在文件处理过程中添加信息的请求，我们只需设置几个系统表并插入一些记录。新属性将立即可用。
演示可以在以下网址看到： 文件处理
Using the Code
主要工作由 Web User Control 完成，显示操作的网页，以及由 WUC 调用（trapdoor）。该控件完全使用 jQuery 工作。它调用 trapdoor 提供的功能来添加或删除选定的属性。根据所做的选择，trapdoor 返回与系统中设置的排除配置相关的有效属性列表。
AttributesProcessingWUC
<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
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(""", "\\"")));
}
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
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;
}