Junction table

[orange]

Thanks for clarifying-- much clearer.
I often ask posters to describe things as per the following scenario:

-pretend you are in a line up at McDonalds (or BurgerKing or your choice) and the person next to you,
who does not know you, does not know your business, has never heard of database
after a few general comments/chat, asks
-what do you do at work? You say building a database
-I have no idea what that is can you tell me more

Anyway your latest post has helped.

After a reading your description a few times, I have identified the following "business facts" that you could confirm or reject or modify.

Divisions are depts...
A Division is responsible for 1 or Many products
Products are made in Batches


A Product may have 0,1 or many Defects(products that have imperfections.)
A DefectiveProduct may be Repairable
A DefectiveProduct does not mean that all Products in the Batch are Recalled
A DefectiveProduct that is not repairable is Recalled

The Company wants to track RecalledProducts.
RecalledProducts are assigned/called an Incident
An Incident records why the Defect was recalled and failed, and whether it can be/was Resolved
A CDE is like a product tag assigned to each defect.

one Incident can have multiple Defects and one Defect can have multiple Incidents

Now I have more questions:

-What does Repairable mean?
-What does it mean to Resolve a Defect?
-Can you give some examples of a RecalledProduct and CDE to clarify what each means and makes them distinguishable?

I'm having difficulty with this one:

one Incident can have multiple Defects and one Defect can have multiple Incidents

Again, perhaps examples would help.

[slimjen]

You are correct on the business facts. Repairable means: lets say there is a batch of product small bottle from Division 1254 that were labeled incorrectly. They are given a Defect# 60000 with a CDE tag C-1234. There may be other bottles in the batch and the top is dented. They are given a Defect# 60001 with the same CDE tag 1234 because its from the same batch. Both of this type of Defects are repairable thus it's resolved. Defects Recalled deemed not repairable: If the product has a dent in the bottle which causes it to become unleveled; this is not repairable so its recalled. They are given a Defect # 89999 with a product tag C-8787 from Division 7896. This is put into the database tracked with an Incident ID and all pertinent information. Caveat: let's say the some of the bottles from the same batch did not have the same dents. They were noted in the database as resolved and put back into rotation. As far as the many:many...Obviously; one Incident can have many Defects but in the case where a Defect may be deemed resolved but when it's put back in rotation for some reason taken out again and deemed not repairable; it is again entered into the database and assigned another IncidentId but will keep the first assigned Id for references. Thanks

[slimjen]

Any assist with this?

[orange]

I edited my previous post to show the revision to the business facts based on your response.

What is the logic for:
"Defects Recalled deemed not repairable"???
Who or what criteria determines if a Recalled Defect (which I think is a Product or possible a Batch of Product with a Defect) is Repairable or NOT repairable???

Is this all done during some "Quality assurance" function or group, or is it recalled from Sellers/Retailers of the Product???

[slimjen]

Correct; the products have to go through QA and they determine...I don't know the logic at this time I can only assume they base their decisions after examining the product and that they follow a set of specific guidelines

[orange]

I have put together a model based on posts. But I am having a problem with Incident and CDE (I called it CDETag).
I'm trying to understand when and how a CDETag is assigned. Defect really means ProductDefect or DefectiveProduct.
In my earlier post I stated a fact as:
A DefectiveProduct that is not repairable is Recalled

But that doesn't seem to flow --- it seems whether Repairable or not is the result of some investigation. So someone or group must be involved in the decision making.

We should be able to take examples of real data and work them through the model repeatedly. The model should be a blueprint for the database structure so need to sort out more facts.

Feel free to adjust/suggest changes to the model.

Good luck.

[slimjen]

Thanks so much for all your help!