資料庫擴充套件表設計過程記錄

　　這兩天需要實現一個動態表單設計，面對著屬性的不確定，要能夠容納不同的屬性進來，之前也接觸過這方面的設計，但是沒有設計好，導致問題太多，這一次參考一些前輩們的經驗後，再次嘗試一番，通過動態設計表結構，以達到任務要求。

 

一、常用動態表結構設計方式

　　1、動態修改表，適應變化。

　　2、預留欄位實現動態表結構(偽動態)。

　　3、將動態屬性全部儲存在一個欄位中，xml或是json格式儲存(版本號+通用列)。

　　4、表結構和表資料分離，xml形式分別儲存表結構和表資料。

　　5、橫向錶轉縱向表(屬性欄位行儲存)。

　　對於這幾種方式，或許不同的選用適用不同的形式，我選擇了最後一種來實現我現有的設計，這種方式個人感覺更加靈活，可以更方便的擴充套件屬性(適合的才是最好的)。

 

二、橫向錶轉縱向表初步設計

　　首先看下橫向表的設計，如果採用橫向表，因為業務的需要，要容納好幾種行業的資訊進來，這樣一來整張表的欄位數將會非常多，從設計或是維護角度來講，這都是一個棘手的芋頭，因此傳統的橫向表設計不能滿足現有的需求了。

　　

　　按照橫向錶轉縱向表的思路對錶結構進行更改，通過設定成鍵值對形式，得到如下表結構。

　　

 　　在這裡可能有一個情況得想清楚了，我們每一次增加一條記錄的時候，記錄內的資訊是作為同一批新增進來的，反過來，當我從資料庫中取出資料時，也應該需要把同一批的記錄資訊取出來，因此在上面的設計中再加入一個GroupId用來區分同一批次的資料，而至於屬性的重複量很大，之後將進行優化處理。

 　　

　　現在看到這個結構時，對於動態擴充屬性來講，已經是達到了我的預期了，對於資料庫設計時，將檢測指標及限值均設定成字串的，分組號我採用時間戳的形式進行儲存，當然也可以採用其它更為穩妥的方式，如Guid或是自定義ID等。

　　對於好多檢測指標名稱出現重複情況，我將這部分單獨抽出來一張表用於儲存檢測指標屬性，需要注意的是此處的名稱需要在某個檢測專案編號下唯一，該部分屬性先在介面上呈現，其次呈現對應的資料，如果某列增加或刪除了，對應展示行也就空著了一個資料或是消失了一個資料單元，而對於改了指標名稱或是對外的展示名稱，都不會影響資料的儲存，通過預設值可以使得有些常用值不要再二次輸入，減少工作量。

 　　

　　在具體檢測限值中完成對檢測指標的關聯，加入一列完成外來鍵關聯，同時對原有表記憶體在的列可以進行優化，因為這些資訊都在檢測指標中存在了，不必要的資料冗餘還是不存在為好。單從現在的表結構來看，當我們按照在增加一些額外的屬性時，可以做到不要去修改表結構，而只需要對錶內資料進行管理即可。

 　　

 

三、程式碼實現過程

　　此處我採用Asp.Net Core MVC並利用Razor語法，更為方便的完成表單展示工作，當然對於這部分工作，採用js或模板等等都是可以快速完成的。

　　1、首先對於介面新增檢測指標的設計，遵循普通的表單設計方式即可，此處增加了兩個隱藏元素，為適用於編輯場景而存在，此處快速略過提交到後臺並儲存到資料庫的過程，可能需要在後臺驗證提交的名稱的唯一性。

<div class="layui-fluid">
    <form class="layui-form" lay-filter="layuiadmin-form-evaluationIndex" style="padding: 15px 0 0 0;">
        <input type="hidden" name="id" value="@Model.Id" />
        <input type="hidden" name="evaluationStandardId" value="@Model.EvaluationStandardId" />
        <div class="layui-form-item">
            <label class="layui-form-label">名稱</label>
            <div class="layui-input-block">
                <input type="text" name="name" lay-verify="required" placeholder="請輸入名稱" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">顯示名稱</label>
            <div class="layui-input-block">
                <input type="text" name="displayName" lay-verify="required" placeholder="請輸入顯示名稱" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">預設值</label>
            <div class="layui-input-block">
                <input type="text" name="defaultValue" placeholder="請輸入預設值" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <div class="layui-input-block">
                <button class="layui-btn layui-hide" lay-submit lay-filter="LAY-evaluationIndex-front-submit" id="LAY-evaluationIndex-front-submit">立即提交</button>
            </div>
        </div>
    </form>
</div>

 　　2、對於增加具體的檢測記錄，需要先讀取到整個檢測專案下的所有檢測指標，然後實現生成表單的過程，按照如下的思路一步一步實現：

 　　

　　對於第一步，從資料庫獲取指定檢測專案的檢測指標，該步可以直接利用提供的id做一次查詢即可得到相應的指標集合。然後在前端迴圈輸出時，利用Razor語法完成動態渲染Html，生成label和input元素，依照之前設計檢測指標時的name唯一，可以在此處設計表單時指定name屬性。

<div class="layui-fluid">
    <form class="layui-form" lay-filter="layuiadmin-form-evaluationLimitValue" style="padding: 15px 0 0 0;">
        @foreach (var item in Model)
        {
            <div class="layui-form-item">
                <label class="layui-form-label">@item.DisplayName</label>
                <div class="layui-input-block">
                    <input type="text" name="@item.Name" value="@item.DefaultValue" placeholder="@("請輸入"+item.DisplayName)" autocomplete="off" class="layui-input">
                </div>
            </div>
        }
        <div class="layui-form-item">
            <div class="layui-input-block">
                <button class="layui-btn layui-hide" lay-submit lay-filter="LAY-evaluationLimitValue-front-submit" id="LAY-evaluationLimitValue-front-submit">立即提交</button>
            </div>
        </div>
    </form>
</div>

 　　接下來可以完成表單的輸入工作了，並提交到後臺完成儲存到資料庫中，如我此處，新增記錄時，儲存到資料庫前，先生成分組號，以此來區分這些指標下的資料是一個批次的，然後完成儲存到資料庫的過程。

public async Task ConvertTableToEvaluationLimitValues(TestItemCode_EvaluationStandardSubItem assignTestItemCode, Dictionary<string, string> evaluationLimitValues)
{
    var groupId = DateTimeHelper.GetTimeStamp();
    var evaluationIndexes = assignTestItemCode.EvaluationStandardSubItem.EvaluationStandard.EvaluationIndexes.ToList();

    foreach (var item in evaluationLimitValues)
    {
        var evaluationIndex = evaluationIndexes.Where(e => e.Name == item.Key).FirstOrDefault();
        if (evaluationIndex == null) continue;

        var evaluationLimitValue = new EvaluationLimitValue(evaluationIndex.Id, assignTestItemCode.Id, item.Value, groupId);
        await _evaluationLimitValueRepository.InsertAsync(evaluationLimitValue);
    }
}

 　　對於這部分的設計，做一點更改也適用於更新操作，但是得注意到，更新表單時，表單上展示的檢測指標可能存在增加或是刪除的情形，因此對於存在的記錄我們可以展示出來，不存在的則留空，當提交到資料庫時，需要做一次比對過程，對那部分增加的檢測指標需要儲存到資料庫中，當然對於已有的檢測指標也存在變更的可能，因此需要做一次判斷，當有變更時更新，沒有時不處理。

public async Task UpdateEvaluationLimitValues(TestItemCode_EvaluationStandardSubItem assignTestItemCode, string groupId, Dictionary<string, string> evaluationLimitValues)
{
    //獲取當前的檢測指標
    var evaluationIndexes = assignTestItemCode.EvaluationStandardSubItem.EvaluationStandard.EvaluationIndexes.ToList();

    //目標分組已存在的檢測限值
    var results = await _evaluationLimitValueRepository.GetAll()
        .Where(e => e.TestItemCode_EvaluationStandardSubItemId == assignTestItemCode.Id && e.GroupId == groupId)
        .Include(e => e.EvaluationIndex).ToListAsync();

    //已存在的檢測限值對應於檢測指標名稱列表
    var existedEvaluationLimitValueNameList = results.Select(r => r.EvaluationIndex.Name).ToList();

    //需新增的檢測限值記錄
    var addEvaluationLimitValueList = evaluationLimitValues.Keys.Except(existedEvaluationLimitValueNameList).ToList();
    foreach (var key in addEvaluationLimitValueList)
    {
        var evaluationIndexId = evaluationIndexes.Where(e => e.Name == key).FirstOrDefault().Id;
        var evaluationLimitValue = new EvaluationLimitValue(evaluationIndexId, assignTestItemCode.Id, evaluationLimitValues[key], groupId);
        await _evaluationLimitValueRepository.InsertAsync(evaluationLimitValue);

        //移除記錄
        evaluationLimitValues.Remove(key);
    }

    //更新已有檢測限值記錄值
    foreach (var key in evaluationLimitValues.Keys)
    {
        var editEvaluationLimitValues = results.Where(r => r.EvaluationIndex.Name == key && r.LimitValue != evaluationLimitValues[key]).FirstOrDefault();
        if (editEvaluationLimitValues != null)
        {
            editEvaluationLimitValues.LimitValue = evaluationLimitValues[key];
            await _evaluationLimitValueRepository.UpdateAsync(editEvaluationLimitValues);
        }
    }
}

 　　3、完成檢測記錄的表格展示，此處需要遵循一個原則，就是先展示檢測指標，也就是先展示屬性列，其次展示資料值，只有相應的屬性列存在，展示的資料值才有意義，通過指定的編號Id獲取相應的屬性集合並展示在前端，利用Razor語法迴圈輸出th元素，來產生表格行頭。

<div class="layui-col-xs12">
    <table class="layui-table"
            lay-data="{height: 'full-100', id:'mainList'}"
            lay-filter="list" lay-size="xs">
        <thead>
            <tr>
                <th lay-data="{checkbox:true, fixed: true}"></th>
                @foreach (var item in Model)
                {
                    <th lay-data="{field:'@item.Name'}">@Html.Raw(item.DisplayName)</th>
                }
                <th lay-data="{fixed:'right', width:240, align:'center', toolbar: '#barList'}"></th></tr>
        </thead>
    </table>
</div>

 　　表格展示完畢時便是資料開始呈現的時機，通過獲取檢測限值中的記錄，注意這裡的記錄會有多條存在的，我們需要將縱向表結構轉換成橫向的json格式，用於前端讀取，注意這裡得把分組號加入進來，這是屬於同一批次的標識。

　　

　　通過縱向轉橫向，可以得到字典型別的list集合，然後再返回前序列化成json格式，便是前端需要的格式。

public async Task<List<Dictionary<string, string>>> ConvertEvaluationLimitValuesToTable(Guid assignedTestItemCodeId)
{
    //分組後的評價限值
    var results = await _evaluationLimitValueRepository.GetAll()
        .Where(e => e.TestItemCode_EvaluationStandardSubItemId == assignedTestItemCodeId)
        .Include(e => e.EvaluationIndex)
        .GroupBy(e => e.GroupId).ToListAsync();

    List<Dictionary<string, string>> convertResultList = new List<Dictionary<string, string>>();

    foreach (var result in results)
    {
        Dictionary<string, string> tempResultList = new Dictionary<string, string>
        {
            { EvaluationLimitValue.GetGroupIdName(), result.ElementAt(0).GroupId }//增加分組號GroupId
        };

        foreach (var item in result)
        {
            tempResultList.Add(item.EvaluationIndex.Name, item.LimitValue);
        }

        convertResultList.Add(tempResultList);
    }

    return convertResultList;
}

 

四、設計實現效果

　　1、實現動態增加屬性列，儘管沒有太豐富的功能，但是已經滿足我現有的需求了，或許還能依據此得到更復雜的表單屬性列設計。

　　

　　2、增加表單具體值，依據增加的屬性列完成相應值填寫。

　　

　　3、再次增加屬性列後增加表單具體值，實現屬性列的增加刪除和修改後，仍然可以適用而無需手動修改表結構。

　　

　　至此，動態表單的簡單設計工作已經完成，過程較為簡單，沒有融入更多的比如多選，單選、數值型的設計，純字元型別設計工作。

 

2019-05-27,望技術有成後能回來看見自己的腳步