如何为这个Jagged数据建模？

I am in charge of modeling a 3 floor warehouse, where boxes of documents will be stored. There are rows, columns and shelves in the equation.

我负责对3层仓库进行建模,其中将存储文件箱。等式中有行,列和架子。

in addition:

some floor/row/column/shelf combinations store 2 boxes, some 3.

一些地板/行/列/架子组合存储2个盒子,大约3个。

some rows don't have the normal amount of columns.

某些行没有正常的列数。

They want my application to auto-increment to print labels (20 at a time) telling where the boxes go as a user scans them in. So box 1 is scanned would print f1r1c1s1b1 box 2 would print f1r1c1s1b2 ... f3r26c26b3

他们希望我的应用程序自动增加打印标签(一次20个),告诉用户扫描时盒子的位置。所以盒子1扫描将打印f1r1c1s1b1盒子2将打印f1r1c1s1b2 ... f3r26c26b3

I was thinking of building a MSSql database, filling it with all the possible combos and subtracting out the exception data. (using vb.net2005 for loops to do the filling) then based on the smalldatetime or perhaps a timestamp column on the table, just grab the next one that doesn't have spotFilled column set.

我正在考虑构建一个MSSql数据库,用所有可能的组合填充它并减去异常数据。 (使用vb.net2005 for循环来填充)然后根据表上的smalldatetime或者timestamp列,只需抓住没有spotFilled列集的下一个。

would this work? is there a better way?

这会有用吗?有没有更好的办法?

(the next step is blocking off the 20 at a time so 2 users could scan boxes without bumping into each other on the same floor/row/column, Most floor/row/column/shelf combos store 21 boxes, 1 bump would probably be ok. also they would like the boxes roughly in the same order received)

(下一步是阻止20个一次,这样2个用户可以扫描盒子而不会在同一个楼层/行/列上互相撞击,大多数楼层/行/列/架子组合存储21个盒子,1个凹凸可能是好的。他们也希望盒子大致按照相同的顺序收到)

MSSQL server and VS2005 are already present in my work environment so those are the tools I am most familiar with.

MSSQL服务器和VS2005已经存在于我的工作环境中,因此这些是我最熟悉的工具。

3 个解决方案

#1

I'm assuming that there's no trivial schema so you can map the whole location vector to a simple integer index? For instance, if it's always the first column that allows 3 boxes, you can still map the f3r26c26s1b3 vector to an integer.

我假设没有简单的架构,所以你可以将整个位置向量映射到一个简单的整数索引?例如,如果它始终是允许3个框的第一列,您仍然可以将f3r26c26s1b3向量映射到整数。

Otherwise, the best solution is probably not to store each and every combination. Instead, assume that each shelf in fact does store 3 boxes and set the "spotFilled" of the third box to a dummy value (-1 or so; anything but NULL=unfilled). This will you to treat this as a normal rectangular array. It only works because your array is almost regular, but hey - real-world IT is all about recognizing the exceptions to the exceptions

否则,最好的解决方案可能不是存储每个组合。相反,假设每个架子实际上存储了3个盒子,并将第三个盒子的“spotFilled”设置为虚拟值(-1左右;除了NULL =未填充之外的任何东西)。您将此视为普通矩形阵列。它只能起作用,因为你的阵列几乎是常规的,但是嘿 - 真实世界的IT就是要识别异常的例外

#2

Why not just have a normal table as if it was non-jagged (columns: Floor, Row, Column, Shelf, Box); put appropriate bounds on the data that will minimize how big the thing db needs to be, and then just store fake boxes in the fake spots.

为什么不只是有一个普通的表,好像它是非锯齿状的(列:地板,行,列,货架,盒子);对数据进行适当的限制,以最大限度地减少数据库需要的大小,然后将假盒子存放在假点中。

#3

Public Function SecondFloor() As List(Of List(Of bDatafield))
    Dim result As New List(Of List(Of bDatafield))
    For Aisle As Short = 1 To MaxAisle
        For Column As Short = 1 To MaxColumn
            If Not SecondFloorExceptions(Aisle, Column) Then
                For shelf As Short = 1 To MaxShelf
                    For Position As Short = 1 To MaxPositions
                        Dim Location As New List(Of bDatafield)
                        Location.Add(MakeNewField("Floor", Floor2))
                        Location.Add(MakeNewField("Aisle", Aisle.ToString))
                        Location.Add(MakeNewField("Column", Column.ToString))
                        Location.Add(MakeNewField("Shelf", shelf.ToString))
                        Location.Add(MakeNewField("Position", Position.ToString))
                        result.Add(Location)
                    Next
                Next

            End If
        Next
    Next
    Return result
End Function

Public Function MakeNewField(ByVal column As String, ByVal value As String) As bDatafield
    MakeNewField = New bDatafield(column, New Nullable(Of Integer))
    MakeNewField.SqlColumnTransformer = New TransformField(AddressOf MapSqlColumn)
    MakeNewField.Value = value
End Function

Public Function SecondFloorExceptions(ByVal Aisle As Short, ByVal column As Short) As Boolean
    If column > MinAisleLength Then
    ElseIf column > MaxColumn Then
        SecondFloorExceptions = True
    Else
        Select Case Aisle
            Case 2 ''//Items with 39
                If column > 39 Then SecondFloorExceptions = True
            Case 3 To 10, 26 To 30, 32 To 38 ''//Items with 41
                If column > 41 Then SecondFloorExceptions = True
            Case 11 ''//Items with 32
                If column > 32 Then SecondFloorExceptions = True
            Case 12, 13 ''//Items with 38
                If column > 38 Then SecondFloorExceptions = True
            Case 14 To 24 ''//Items with 36
                If column > 36 Then SecondFloorExceptions = True
            Case 25, 31 ''//Item with 35
                If column > 35 Then SecondFloorExceptions = True

        End Select
    End If
End Function

Then I dumped it into the database with:

然后我将它转储到数据库中:

Public Sub InsertLocationRow(ByVal cn As bInheritance.bCnNativeMs _
        , ByVal data As List(Of bDatafield))
    Dim leftSide As String = "insert into " + My.Settings.ProjectSchema + "." + My.Settings.tblLocations + "("
    Dim rightSide As String = " values ("
    Dim first As Boolean = True
    For index As Integer = 0 To data.Count - 1
        If data(index).isValid Then
            If Not first Then
                leftSide += ","
                rightSide += ","
            End If
            leftSide += data(index).SqlColumn()
            rightSide += BLib.AddQSafe(data(index).Value, True)
            first = False
        End If

    Next
    leftSide += ")"
    rightSide += ")"

    cn.ExeNonQuery(leftSide + rightSide)
End Sub