Need a reference. Help.

I'm designing a BOM / Inventory system to interface to our Manufacturing Order Entry System. My mind is boggled.

I've been looking around for a database design that would suit us. Anyone have a link?

Here's some details, in case you're interested. In short; a simple single-dimension solution won't do.

We make window coverings (blinds).

Here's the problem - we have an established Order Entry system where 1 line item specifies a basic blind and all the upgrades they want. Some upgrades are mutually exclusive of other upgrades - or product sizes - or some other facet of the build. For example; if they order a cellular shade, it normally has a plastic headrail mechanism. However if they request a motor, we cannot use the plastic headrail.

Anyway; we're so embedded into the current system there is zero chance of replacing it. We have to back into the current system.

Also; most of the components are the likes of "6 screws, 2 tassels, 20 feet of string, 3 10 cent brackets, etc." so there is no chance of scanning it at all 50 assembly stations. In fact; we don't have ANY bar code scanners in the factory or shop floor, and no chance of any.

So; we have to reduce inventory based upon orders coming from the current system.

I believe I need to cover the most complex situations of many-to-many-to-many(and sometimes zero) relationships.

Many components (like string) are used by all build types, and many of the options of a build are just that, optional.

Then there's a question of COLOR. We have many colors of just about everything, but we don't have ALL colors of everything. For example; we have about 300 fabric / slat patterns, but only 20 string colors, 5 headrail colors, 3 bracket colors (beige, white, and zinc), etc.

Then there's the matter of "how much". Some components are simple "1 per blind, 2 per blind, identical to number of inches of width, etc", but some are more complex "2 slat ladders if under 50 inches, then 4 ladders if between 50 and 80 inches, then 6 if over 80".

So; I need to design a BOM (and Build Definition) capable of defining "what component, what color (really the same as what component), and how much".

Finally; it has to be maintainable. IE: They add new builds or changes to existing builds about every 2 months.

So: It occurs to me that maybe someone has already figured all this out! Simplicity seems to be the key and so far, my solutions are very complex.

A link, or even white paper or book recommendation, would be emensly helpful. Even a recommendation of an existing BOM (or WMS) that has APIs that could satisfy this would work. Problem is that all I've found require using their Sales Order system and that's just not feasible.

Oh; we are using MS SQL Server.

Thank You Thank You Thank You!

Sorry to bore you all with the overlong post.

I was just looking for a link to a BOM database design. Any takers?

Hi,

Here's an interesting Data Model on the DatabaseAnswers.org web site that might help you to get started :-
http://www.databaseanswers.org/data_.../bom/index.htm

Good help.

B.Dimple
Senior DBA

Hey, thanks B.Dimple!

That site, in general, looks very helpful. The model you linked to was only partial to what I need, but having a starting point gave me some wonderful solution ideas.

I'll tell ya, "divide and conquer" is sometimes easier said than done, particularly in a 1-man-shop.