How can I model this Jagged data?

Question

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.

in addition:

some floor/row/column/shelf combinations store 2 boxes, some 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

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.

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)

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

Answer 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.

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

Answer 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.

Answer 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