Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Need help designing a Bill of Material

    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!
    Last edited by vich; 10-10-06 at 21:05.

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Sorry to bore you all with the overlong post.

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

  3. #3
    Join Date
    Jul 2003
    Posts
    74

    Data Model for BOM

    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

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by bdimple
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •