I'm looking for some assistance with my data model regarding multi-level parts + variable parts. I've reviewed the various starter files (FM Starting Point and FMIS) but was not able to see the following scenario in action.
We have "Products" that are sold to us by a "Vendor." The Products themselves are made of different, basic "Parts," which are actually made by a handful of different "Manufacturers." The "Parts" have various potential attributes, that may or may not apply to similar Parts (more precisely, one attribute may have 6 versions, only 3 of which apply to Part A but all 6 apply to Part B).
What I can't figure out is the best way to create and relate the various attributes to the individual Parts (and the subsequent Products made from / related to those Parts). I thought of using Join Tables, but it started getting messy quick and I didn't make intuitive sense in some situations.
Additionally, I wasn't sure how the correct Attributes of a given Part would flow through to the final Product (which may not utilize all available attributes of the base Part).
Here is an example:
We have a Product called, let's say, "T-Shirt 1" that is sold to us by Vendor X. Vendor X buys the Part "Crew neck" from Manufacturer Y. Part "Crew Neck" comes in Size (an Attribute) "Small", "Medium", and "Large." It also comes in Color (another Attribute) "Black" and "White."
Manufacturer Y might also make a Part called "V neck" that comes in the same Sizes as "Crew neck", but only in Color "Green." Or the same Colors, but only Size "Small."
Product "T-Shirt 1" may be made of Part "Crew neck", but we only offer Color "Black" in that Product (though we may have another Product that uses the Color "White" of Part "Crew neck"). Or we might offer two versions of Product "T-Shirt 1", one in each of the two Colors.
If a Product is offered in multiple Attributes, such as Color, there might be additional Attributes that apply only to those specific combinations. For example, a Product might be offered in every available Part Size, but Size "2XL" results in an price increase (for that size only). Or Product "T-Shirt 1" in Part Color "Black" might need Ink Color (a Product-specific Attribute) "White" but the same Product "T-Shirt 1" in Part Color "White" might use Ink Color "White".
I hope that makes sense. It seems like a pretty common DB setup for any sort of inventory system with variables, but most of what I've seen have just used Value Lists for Attributes like Size and Color. The problem with that is different Parts may have Part-specific attributes (specifically, Manufacturers don't offer the same Colors for their Parts). This starts getting really confusing when we think along the lines of "I like this Color from this Manufacturer...but they don't seem to offer this Color in this specific Part that we use for this specific Product."
The point of this DB will be to track all of the various Products we sell and know everything we can about each specific Product (which is why there are so many Part and Product-specific Attributes and they may increase / change over time). This is not a Sales system, but having insight into how Products change over time would be nice (we use to order this Product from Vendor X, but now Vendor W sells the exact same thing to us...or it is the same end SKU for us, but it uses a slightly different base Part).
If anyone could help me conceptualize the right data model for this, it would be greatly appreciated. I have some FM experience and have a basic system for this running, but I know the data model is not right and the most important part, the Attributes, is missing (after several attempts).