Should I add fields or a table?

[Gina K]

I have a pretty simple order management database with the following tables:
tblOrders
tblLineItems
tblProducts
tblCustomers

For each order in tblOrders, I want to add a “routing†and a “checklist†for
each of 11 departments to sign off when they’ve reviewed the order. One way
I can think of is to add 33 fields (like SchedulingDueDate,
SchedulingCompleteDate, SchedulingCompletedBy, OrderEntryDueDate,
OrderEntryCompleteDate, OrderEntryCompletedBy, DetailingDueDate,
DetailingCompleteDate, DetailingCompletedBy, etc.) to tblOrders, but this
does not seem like good database design.

I also thought about adding a table called tblDeptSignoff with fields like
fkOrderID, Department, DueDate, CompleteDate, and CompletedBy, but then how
can I be sure that a record is entered for all 11 departments in the list?
Would I use an append query?

I’m really lost here…

 

[Klatuu]

A separate table would make sense only if one of these statements is true:
1.The number of departments or the names of departments may change some time
in the future.
2.Not all departments are required to sign off on every order.

If you go with a separate table as described, you can determine how many
deparments have signed off using a DCount function:
=DCount("*","tblDeptSignoff","[OrderNumber] = " & Me.txtOrderNumber)

If all departments have to sign off on each order and the number of
departments will always be the same (every time I ever thought something
would never change, it did), then there are two options.

One would be to just add the fields to tblOrders. The other would be to
create your tblDeptSignoff, but put all 33 field in the table.

The second table would only be a good idea if once the order is complete you
have no need to maintain the data in tblDeptSignoff, you could delete the
record to save disk space and mdb size. It would, however, make your coding
a bit more complex.

BTW, a hint about naming. Your table named tblLineItems. What line items?
Purchase Order line items? Sales Order Line Items? Invoice Line Items?

I would suggest table naming that makes it clear what the table is used for.
for example:

tblSalesOrderHeader
tblSalesOrderLineItems (or, I usuall use tblSalesOrderDetail)