product db question

[overrider]

Hello all,

my goal is to have a Product / Part Database, which only needs one unique Number, the Part Number, while still allowing for different Vendors which might offer better price conditions, leadtimes etc.

Before we had it this way: A Part with two Vendors would have had two Records in the item database, of which both had the same Part Number, but a unique Item ID, which was an autogenerated unmodifiable serial. The Partnumber was self joined, so i had a Portal on my Layout showing alternative Vendors that way. There are a lot of Quirks with this, one of it is here: We use a BOM to build a Product out of multipe Parts. Now say i need 10 pcs of a SCREW (part number), for which i have three Vendors. On the BOM i like to put the part number "SCREW" 10 pcs. However, in our scenario i have 3 different occurances of the part number SCREW in my item database, one for each vendor. So i have to have the Item ID which is unique mapped to an Item ID in the BOM File, and have the Person who makes the BOM select the right of the three Item ID`s when entering into the BOM. Three different Item ID`s, though they are totally the same Product, just a different Vendor.

Solution #1:

I have my item Database, which has only one unique partnumber per Product / Part, and contains information such as specification, categories and such. Then i make a new Table item_vendors which contains vendor_id, price, leadtime and moq. I make a relationship from the item db to the item_vendors table mapping the partnumber to the partnumber. Now i can via a Portal in the item db add multiple vendors for one item, without having that item multiple times in my database. But is this the right way? What is if not only the Vendor is different pricewise, but while delivering the exact same product, packs it into different quantities per carton? shall i now also have the packing information in the items_supplier table? It all seems not a very solid solution.

If anyone know my meaning, i would be happy for any suggestions. I can also try to explain it again with example files if that would be better.

Thanks a lot for suggestions,

[overrider]

hello all,

it seems i didnt explain my problem well, or maybe the issue was too obvious.

the problem was:

in our item database each item had a unique number, called the item id. it also had a partnumber. so if you had the part AB-1234 with two different vendors, you would have two records in your item database, both with the same partnumber but different item id. this has created a lot of different issues.

the solution:

unless you have a specific reason not to do it that way, just make use of relational database design. create a seperate table called f.e item_vendors, and there create a record for each vendor you have for an item.

i assume this should be done this way, just as you wouldnt duplicate a customer record to record his second or third address, you would make a table to record his addresses.

honestly right now i cant think of why our item_db hasnt been designed that way in the first place, and why i didnt think about it when i saw it first.

[Fenton]

I see you've answered yourself, and you're absolutely right. A join table was needed, and any product-vendor specific info goes there. It is pretty easy in FM 7/8 to read date through the join table, when needed.