I have been searching in vain for some sort of class library which will have the sort of DataBase tools I am looking for, but I can not seem to find anything. Perhaps I do not know how to describe and search on the task. In any case, here is what I am looking for:
A Dynamic Field Based Structure for ERP Item (Part) Numbers: Modern Engineering Practice holds that items should have unique non-significant part numbers. Here is a brief primer on part numbering.
https://en.wikipedia.org/wiki/Part_number[
^]
So, if the part number holds no data, how do you find the part? The answer is fields or attributes. The problem is that no modern mfg uses parts that all have the same field structure. If the first field in the item master data table is say "Major Part Type", by the time you get to the second or third field (row in the table), the field title (left most column in the table) has a different meaning.
For example, lets assume we have two items....a fastener...and a pump drive shaft.
The pump shaft fields might be the following:
Field 1 = Major part = SHAFT
Field 2 = Pump Model = 12GM
Field 3 = Impeller Connection = KEYED
Field 4 = Sleeve = NONE
Field 5 = Material = 316L
The text description which would appear in the ERP system would be the following:
SHAFT, 12GM, KEYED, NONE, 316L. This is created by using a comma delimited form which simply pulls the descriptions from rows 1,2,3,4 etc.
Now the bolt
The pump shaft fields might be the following:
Field 1 = Major part = FASTENER
Field 2 = FASTENER TYPE = SHCS
Field 3 = THREAD = 3/4 UNC
Field 4 = LENGTH = 3"L
Field 5 = Material = 316L
The text description which would appear in the ERP system would be the following:
FASTENER, SHCS, 3/4UNC, 3"L, 316L
The problem is these two items need to reside in the same data base. Once you get past the 1st field in the database table, the field designation changes. For the shaft, the second field is for the "pump model". For the fastener, the second field is for the "fastener type".
This is such a common problem in database (SQL) based item identification, but I can not find an elegant solution. I know that answer has something to do with not giving a fixed title to fields beyond row 1, and having a secondary table of field titles, but it sure seems like someone would have created a ADO.Net class library to handle this issue.
Virtually every mfg ERP system I have ever seen still suffers from inconsistency in how the item description is crafted. Invariably, the same part gets entered multiple times:
SHAFT, 12GM, KEYED, NONE, 316L
SHAFT, KEYED, 12GM, NONE, 316L
Etc.
Multiply this by 50,000 part numbers...and you have a complete mess and no one can find anything.