OK. Here's some food for thought and I suspect it does still point towards an EAV model.
We have an ERP system for process management. I just spoke with our General Manager (who has blessed our QC effort) and asked him what he would like to be able to do with our QC db.
He wants (and he is correct too) to be able to call up a QC report based on what we call Job/Task/Resource (JTR). This looks like: A626707001. The "A" is a prefix with little to no meaning. Actually it does have a meaning. We'll get to that in a minute.
This almost looks like a primary key to relate all manufacturing activity by - - but it is misleading.
The next four numbers (6267) are the literal Job Number. The number assigned by Sales when a job is sold and put on the books. This where it gets interesting.
The next three numbers are the "Task." "070" in this case but it could be "100" or on a huge job "999" theoretically. However it is never 001 - 009. It is always at least 010. This is a string identifier by the way - just to be clear. The last two numbers ("01" in the above example) are the "Resource."
Task and Resource are determined by our Detailing/Engineering department. Both refer to spheres of activity related to project sequencing - but both - and especially Resource - are not specifically defined. Task very much refers to a stage of production sequencing. That said, Tasks are not necessarily accomplished in sequential order.
Our decking is used primarily (98% of the time) for commercial roofing. It's innovative in that when two sheets are welded together top and bottom the roof decking instantly becomes the ceiling for the area below.
All that said, Detailing will look at a plan view drawing of an arena or some such structure, talk to the end customer, and decide how to break the job up per the customers intended installation timeline. This typically defines "Task" but even that is not carved in stone. 99.9% of the time it is though.
Resource is where the idea of designing the db around JTR falls apart I suspect. A resource may contain multiple types of deck which require different types of QC inspections. For example, in a single Task, all deck will be welded requiring a welding inspection. A few of those deck pieces may have doors doors installed in them (fabrication inspection). Some but not all of it MAY have acoustic insulation or air dams installed inside of them (assembly inspection). Some of it may be galvanized and some of it may be painted (coating inspection).
In other words, within the JTR number, all of the above processes/inspections may happen - or not. Welding will always happen - but none of the other types of inspection are a given.
And I shouldn't say welding will "always" happen because we ship quite a lot of deck that is simply roof support - a single unwelded/fabricated deck - which is an entirely different inspection (mill inspection) at the rolling mill but is still called out by JTR. In fact, mill/piece inspection happens for 100% of all JTR. But some JTR will have a one series of mill inspection (simple decking) and some will have two occurrences of mill inspection (welded decking).
Then there is a dunnage/freight inspection. Give a specific profile of decking this will be 100% specific for the JTR.
Within each of the above processes, there is consistent inspection criteria. In other words, all criteria for welding inspection are a known and constant. Same with Fabricating and the others.
To reiterate, our General Manager wishes to call up a report on any JTR and view what QC inspections have been made.
Now I am going to "think out loud" and type at the same time - so don't flame me.
This is the "Socratic Method" for me. (In other words I have no idea what I am talking about - lol). All I can think this means is that I will have a table called "tblEvents" that has an autonumber PK and a field called "JTR" that accepts the JTR (A626707001 for example). frmEvents would also include Time, Date, Inspected By fields as well as a couple other general identifiers. I will have a table each for "Mill," "Welding," "Fabrication," "Assembly," "Coating," and "Shipping."
Visually, we would have a main form (frmEvents) based on tblEvents. I would create an inspection form for each of tblMill, tblWelding, etc. Each of those forms would reside in a tabbed form embedded in frmEvents.
Keep in mind that I admit I don't know that this is a good design - I just want to share the only thing I personally can conceive of and hope someone has a better idea.
When we do our inspections on any one of multiple workstations producing various products concurrently - and usually for different and unrelated JTRs - we will first enter the JTR in a main form and then select the tab for the appropriate inspection process (welding, mill, coating, etc.) and fill in the data for that particular inspection. It is important to note that we do multiple inspections over time. For instance, we may start rolling steel for a given JTR at noon and do a first piece inspection. Production may continue for a couple of days on that specific JTR process. We will do spot inspections many times - which will mean that each inspection will become a new record in tblEvents resulting in many, many records with the same value in JTR.
Also - and this is really nutty - in the case of decking that goes through ALL of the above-mentioned production processes - the JTR always stays the same. So there will be even more records in tblEvents with identical values for JTR.
The general manager wants to run undefined as yet reports on inspections by JTR. More than likely, he will want see frequency and type of incidence of non conformance for the entire JTR for the most part as well as any related notes.
I'm sure there are more details but that is the general concept.
I am of course so curious what you all think?
Thanks,
Tim
ps. I forgot about the "A" at the beginning of the JTR. That is an indexing number that will change to "B" when the company sells one more job than 9999.
