August 15, 2011 at 3:19 pm
First of all, thanks a ton for all the good work you are all doing on this forum.
This is my request: I am trying to make an estimator application for a client whose business is fabricating aluminium casements - windows, doors, cabinets, partitions, etc. he needs a program in which he can prepare an order for a client, specify which products are needed, their dimensions and other attributes and the quantity of each product and the software should compute the precise quantities of each raw material together with the cost.
The raw material and costs are held in the Categories and Materials tables, with support from Units and Conversion tables.
The customer and order data is held in the Customers, Orders and OrderDetails tables.
The items (windows, doors, etc) themselves are in the Products table, each item's component (frame, shutter, vent, screen) make up the Components table.
Components are further broken down into Parts (top, bottom, left, right, rivets, rollers, etc) and then each part is linked to one material and a formula to calculate its requirement.
The attached relationship diagram, albeit a shell, represents my most clear attempt so far at the schema design.
1. I am stumped as to how I can use the product details on the order details table to drill down to the formulas and compute the requirements.
2. Assuming I can drill down and find the requirements, how should I save them?
3. How can I present the users with different levels of grouping to show requirement summaries for each product, each material type, etc.
4. Can the schema be tweaked further?
5. Some stock items come in a single size/packing only. Others come in multiple sizes/models, the prices are not proportionate and so the unit costs are different. For some of these items, the end use is the same for each size variation (rubbers, screw packs, etc), while for others (e.g. glass sheets), the size of the product being fabricated determines which size is needed. (a) Since precise costing is required, how can I factor this in if I am offering the user only generics of each stock item? (b) If the client decides to request stock control later, how can I select the balance of stock items of different sizes to be adjusted?
I am designing this whole thing in MS Access 2007.
Many thanks in advance.
August 16, 2011 at 2:24 pm
August 16, 2011 at 3:59 pm
goodguy (8/16/2011)
Anyone home?
Yes and no. Not a small order to contemplate, and in-depth work at the architecture level is usually paid well for. 😉
First off, it sounds like what you're trying to build is a Bill of Materials table. These are recursive, self-joined structures so that you can build it to whatever node depth you need, and still be able to roll it up. Think Org Charts. There's a lot on the web on this topic that you can research.
Second, MS Access is a bear to try to code against. That's another reason you probably haven't seen a lot of responses. For recursion, you're going to be doing VBA code work. God bless and good luck. It's been years since I've done it and wouldn't want to try to remember.
I'll try to give you some headway into where you're going though on your particular questions, though to be honest I'm not downloading the zip, I'm paranoid.
1. I am stumped as to how I can use the product details on the order details table to drill down to the formulas and compute the requirements.
You're definately going to need a combination bill of materials tables and possibly some mathmatics to interface with the final results (see #5)
2. Assuming I can drill down and find the requirements, how should I save them?
I wouldn't. I'd real time calculate them, or if you want point in time estimating, you'd have to save a copy of the materials involved to a second table (call it estimated at time of order or something) that will freeze the materials table in place for that particular order.
3. How can I present the users with different levels of grouping to show requirement summaries for each product, each material type, etc.
This comes back to the recursive and node nature of the BoM table.
5. Some stock items come in a single size/packing only. Others come in multiple sizes/models, the prices are not proportionate and so the unit costs are different. For some of these items, the end use is the same for each size variation (rubbers, screw packs, etc), while for others (e.g. glass sheets), the size of the product being fabricated determines which size is needed. (a) Since precise costing is required, how can I factor this in if I am offering the user only generics of each stock item?
You'd usually have to determine a standardized 'portion' of any item. Let's take glass panes. Instead of a 5x5 sheet, you'd have to price it 1x1 and multiply by 25 (the area). However, since you mention the prices aren't proportionate to the size, you're fubar. You'll have to create discreet entries for each item.
(b) If the client decides to request stock control later, how can I select the balance of stock items of different sizes to be adjusted?
This would need more description. I was answering this and realized I'd made a hundred assumptions about what you meant, which can only lead to confusion.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 17, 2011 at 5:52 am
I'd suggest doing a search online for Bill of Materials database designs. There are a bunch out there because it's a common problem. Use one of them as the basis for your design and then tweak it in as necessary. No need to invent the wheel.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2011 at 7:43 am
Craig, many thanks for taking the time to give a detailed reply. Very much appreciated.
1. Access is a pain, but the only reason I chose it is because I was counting on its fast prototyping to do some quick mock-ups for the client.
2. Thanks for confirming my feeling about saving to a separate table.
3. For some reason, recursive, nodes, and self-joins in database tables give me the creeps! But I will research as you suggested.
5(b). By stock control, I mean maintaining inventory by recording inward deliveries, issuing stock for job orders and updating stock balances.
Many thanks.
August 17, 2011 at 7:46 am
August 17, 2011 at 3:08 pm
@Craig: Having looked at the joys and pains of recursive tables and self-joins, and considering that I know my db has exactly 8 nodes, I feel I would be better served by skipping recursion. What would you advise?
August 17, 2011 at 3:31 pm
If you know there's a fixed depth to the recursion (and you can certainly place that in a prototype), then you can break the recursive bill of materials table into multiple tables, one for each level.
level 0 -> level 1 -> ... level n
You can enforce relational integrity between each of the tables, and not have to worry about recursion.
The recursion isn't that bad a thing, btw... if you treat each self join as a "different table" in your head. You can also use views (saved queries in Access) to encode a similar "level" table over the top of a self-joined recursive table. But if you are stuck for time, breaking it into seperate tables may get you moving forward.
There's no reason you couldn't use Access as a front end over SQL Server as a back end also, even for rapid prototyping. That would give you the SQL Server functionality for data access, and the Access functionality for front-end and reporting.
August 17, 2011 at 3:31 pm
goodguy (8/17/2011)
@Craig: Having looked at the joys and pains of recursive tables and self-joins, and considering that I know my db has exactly 8 nodes, I feel I would be better served by skipping recursion. What would you advise?
If you build a standard hierarchy here, you'll have to build 8x as many procs for each node level and have the front end determine which one to call based on the node level they wanted to report on. This is up to you. It's a question of maintenance vs. ease of construction.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 18, 2011 at 1:13 am
August 19, 2011 at 6:14 am
Hi,
I have skimmed your post and Evil Kraig F is pretty much bang on - with one exception.
What you are trying to do is not standard a standard BOM solution, but "configured" e.g. the atributes of the final product determine the amount of material to use.
You should take a look at BOM use in a "make to order/design to order" situation, but the bottom line of your requirements will be a rules engine to create the BOM for the shop floor e.g. 2mx1m door = (2*2m sides + 2*1m top/bottom) = 6m box aluminium.....
As a general guide:
1) break up your products in families or those that are "similar"
2) Define atributes that are variable (the configuration)
3) Define rules for BOM, routing and pricing. Costing will come from the actual costing from the shop floor control
4) Build rules engine ....... NOT a small job
The result will be a BOM/Routing built from the order atributes rather from the usual make to stock type of situation.
Anyway, I hope this helps a little.
Cheers
Peter
PS - Access ! Oh My God !! .... good look with that!
I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz
August 22, 2011 at 3:48 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply