How should I be handling warehousing scan input in a SQL Server Database?

Question

I've been tasked with organizing a list of scan entries so they can be better analyzed by managers. The list looks similar to this:

+-------------------+-------------------------+
|       SCAN        |        TIMESTAMP        |
+-------------------+-------------------------+
| JOHN&000123       | 2020-02-12 12:40:31.000 |
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 |
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 |
| JOHN&002124       | 2020-02-12 12:49:27.000 |
| JOHN&321 ABC-712B | 2020-02-12 12:49:30.000 |
| JOHN&FINISH BUILD | 2020-02-12 13:00:55.000 |
| GINA&000192       | 2020-02-12 13:00:57.000 |
| GINA&182 BAH-Q293 | 2020-02-12 13:00:58.000 |
| GINA&FINISH BUILD | 2020-02-12 13:07:58.000 |
| GINA&FINISH BUILD | 2020-02-12 13:07:59.000 |
| JOHN&000192       | 2020-02-12 13:08:05.000 |
| JOHN&127 BAH-Q019 | 2020-02-12 13:08:08.000 |
| JOHN&FINISH BUILD | 2020-02-12 13:11:14.000 |
| MIKE&000192       | 2020-02-12 13:11:19.000 |
| MIKE&289 BAH-Q019 | 2020-02-12 13:11:21.000 |
| MIKE&378 BAH-Q019 | 2020-02-12 13:11:28.000 |
| MIKE&FINISH BUILD | 2020-02-12 13:21:50.000 |
+-------------------+-------------------------+

Each scan has a Username concatenated with some sort of code. Purely numeric codes, e.g. 000123, are Order #s. Longer codes, with alphanumeric characters, e.g. 123 ABC-1647, are Item #s. And Finish Build means the order was assembled. Orders are all limited to one item at a time.

Ideally, a standard set of data would look something like this:

+-------------------+-------------------------+
|       SCAN        |        TIMESTAMP        |
+-------------------+-------------------------+
| JOHN&000123       | 2020-02-12 12:40:31.000 | Order
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Item
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 | Finish
+-------------------+-------------------------+

But occasionally we'll get sets like this:

+-------------------+-------------------------+
|       SCAN        |        TIMESTAMP        |
+-------------------+-------------------------+
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Item
| JOHN&000123       | 2020-02-12 12:40:31.000 | Order
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 | Finish
+-------------------+-------------------------+

+-------------------+-------------------------+
|       SCAN        |        TIMESTAMP        |
+-------------------+-------------------------+
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Order
| JOHN&000123       | 2020-02-12 12:40:31.000 | Item
| JOHN&321 ABC-712B | 2020-02-12 12:49:26.000 | Order
+-------------------+-------------------------+

I want to make this system as easy and as helpful as possible. We can train our employees to be more careful with the scanner, but mistakes happen and I wanted to know what would be the best way to organize this information for all parties.

Currently, after learning more about T-SQL, I have arranged the information to look more like this:

+-------------------------+----------+----------------------+----------+--------+----------------+-------+
|      ScanTimestamp      | Username |      ScanValue       | ScanType | IDCol  | IsPossibleDupe | SetID |
+-------------------------+----------+----------------------+----------+--------+----------------+-------+
| 2020-02-12 07:00:03.000 | JOHN     | 000123               | ORDER    |    688 | NULL           | 1     |
| 2020-02-12 07:00:03.000 | JOHN     | 123 ABC-1647         | ITEM     |    689 | NULL           | 1     |
| 2020-02-12 07:09:09.000 | JOHN     | FINISH BUILD         | FINISH   |    690 | NULL           | 1     |
| 2020-02-12 07:09:10.000 | JOHN     | 002124               | ORDER    |    691 | NULL           | 2     |
| 2020-02-12 07:09:11.000 | JOHN     | 321 ABC-712B         | ITEM     |    692 | NULL           | 2     |
| 2020-02-12 07:24:06.000 | JOHN     | FINISH BUILD         | FINISH   |    693 | NULL           | 2     |
| 2020-02-12 07:24:08.000 | GINA     | 000192               | ORDER    |    694 | NULL           | 3     |
| 2020-02-12 07:24:10.000 | GINA     | 182 BAH-Q293         | ITEM     |    695 | NULL           | 3     |
| 2020-02-12 07:45:06.000 | GINA     | FINISH BUILD         | FINISH   |    696 | NULL           | 3     |
| 2020-02-12 07:45:12.000 | GINA     | FINISH BUILD         | FINISH   |    697 | 1              | NULL  |
| 2020-02-12 07:45:13.000 | JOHN     | 000192               | ORDER    |    698 | NULL           | 4     |
| 2020-02-12 07:45:14.000 | JOHN     | 127 BAH-Q019         | ITEM     |    699 | NULL           | 4     |
| 2020-02-12 07:56:36.000 | JOHN     | FINISH BUILD         | FINISH   |    700 | NULL           | 4     |
| 2020-02-12 07:56:39.000 | MIKE     | 000192               | ORDER    |    701 | NULL           | 5     |
| 2020-02-12 07:56:40.000 | MIKE     | 289 BAH-Q019         | ITEM     |    702 | NULL           | 5     |
| 2020-02-12 07:56:47.000 | MIKE     | 378 BAH-Q019         | ITEM     |    703 | NULL           | 5     |
| 2020-02-12 08:30:11.000 | MIKE     | FINISH BUILD         | FINISH   |    704 | NULL           | 6     |
+-------------------------+----------+----------------------+----------+--------+----------------+-------+

I feel like this is a bit better to read, but I feel like we can do better. You can also see from the example above that there are some "Sets" of data that do not follow the expected format of Order -> Item -> Finish. I am still building out how to handle some of these scenarios but some are just completely ambiguous and require manager input, e.g. Order#, Item#, Item#, Finish - There can only be one item per order, so which is the correct item?

I was hoping to eventually condense the ORDER ITEM FINISH rows into their own columns, but I wanted some input before I put more time into whether this is even a good approach to this problem:

+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+
|        ItemTime         |        OrderTime        |       FinishTime        | Username |   Item#   |     Order#      | IsPossibleDupe | SetID |
+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+
| 2020-02-12 07:00:03.000 | 2020-02-12 07:00:03.000 | 2020-02-12 07:09:09.000 | JOHN     | 000123    | 123 ABC-1647    | NULL           |     1 |
+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+

Answer 1

Caveat: I am a primarily Microsoft SQL Server DBA, and your environment may differ, requiring adjustments to make my advice work for you.

One approach to this might be to write up what needs what in this. An example would be that a user can create an order. An order has a start time and an end time. These can be in the same table, with the FinishTime just being NULL until it's done. An order item can be attached to an order using a JOIN table. Here's what that could look like:

EntryID OrderID ItemID TimeStamp -------- ------- ------ --------- 11121 123 ABC 2010-01-01 11:30 11122 123 EEE 2010-01-01 12:00

This way when you want to view the data, you can get one row from the Order table (OrderID=123) and all rows from the ItemTable (WHERE OrderID=123). And remember, relational data isn't stored in any particular order. It's all just stored in relation to other data. (eg. John isn't the third name in the list, it's just associated with Order 123) It's up to the display layer to make it make sense.