If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Should I add fields or a table?
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… |
#2
|
|||
|
|||
Should I add fields or a table?
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) -- Dave Hargis, Microsoft Access MVP "Gina K" wrote: 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… |
Thread Tools | |
Display Modes | |
|
|