Thread: Need some DB structure help
12-22-13, 23:12 #1Registered User
- Join Date
- Nov 2013
Unanswered: Need some DB structure help
Good Evening DBer's,
I am currently designing and eventually developing a small DB in Access 2010 for a local manufacturing company (job shop) that I do part type bookkeeping for currently.
The design issues I am having a hard time wrapping my head around is two fold.
1) The first is Purchase Order line items and work orders (3 tables: POrder's, Line Items, & WorkOrders). The issue comes from the fact that some POrders's may come in with varying degrees of line items in the following scenarios:
A: 1 Line Item with a single delivery date (common - 1 Line Item --- 1 Work Order).
B: 1 Line Item with multiple delivery dates (rare - could be 1 Work Order for each date or single work order with multiple deliveries (preferred)).
C: Multiple Line Items (same part #) with single delivery date (rare - Would prefer to use 1 Work Order with total qty of all Line Items).
D: Same as C with multiple delivery dates (somewhat common - Would prefer to use 1 Work Order for total qty, but list each qty with due dates).
E: Multiple Line Items (different part #'s) with either same or different delivery dates (common - Each Line Item --- 1 Work Order).
***see first attachment for my current thoughts...any help would be appreciated.
2) The other issue is between Work Orders and Sub-Jobs (for sub-components) and creating Routing Travelers from either. Some work orders will never have sub-jobs and Routing Travelers need to be available for both Work Orders (w/o sub-jobs) and Sub-Jobs (3 Tables currently: WorkOrders, SubJobs, & Routing). Can the relationships work the way they are set up in the second attachment with a 1-to-1 relationship and allow nulls in the fields for a Work Order if it is a SubJob routing or vice versa?
***see second attachment for my current thoughts on this issue.
Thank you for any time in advance and Happy Holidays to all!
12-23-13, 05:48 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
A PO is made up of line items, so a natural key for the line items is the PO number AND the line number ( as a composite PK... identify both columns as PK's), instead of creating a new surrogate key LIID. one advantage of this is that it then becomes impossible to duplicate the same PO number AND line item number.
Id suggest a certain amount of separation between the PO Order and the work order.
The translation between multiple delivery dates/quatnities etc comes when the item is realease for production. thats where I'd expect quantity and delivery dates to be set. if there are separate delivery dates then probably there should be separate work ordersI'd rather be riding on the Tiger 800 or the Norton