I think you have a good start on this. The main thing that jumps out at me to improve the schema is the need to distinguish between things and types of things. In the requirements there is really both a product type - say a model 250 computer - and products - assembled instances of model 250 computers. The product type is known by a model number, whereas the product is known by a serial number stamped on an instance of that model to distinguish it from another assembled instances. Likewise, there is the knowledge that to assemble a product of a given product type certain types of parts are required - say a motherboard or a memory chip. There are many parts purchased or built of each part type - say each with its given serial number - which are then used in the assembly.
The reason this distinction is critical is because it is products and parts that are assembled and tested, and it is product and part types about which we want to know the number of failures, attempted repairs, and replacements. Let's walk through the requirements.
Products and Parts and Types
The database is intended to capture all product failures and
corresponding repairs. A product can fail multiple times and can be
repaired multiple times(many to many relationship). Not all products
fail, and no data will be captured for products that don't fail.
It sounds like it is a product type whose products can fail multiple times and be repaired multiple times. By multiple times we mean that while each product of a given product type can fail and be repaired only once, there are many products per product type which can fail and be repaired.
A good way to visualize the schema is to use an ER Diagram. Here is a diagram, using Oracle Data Modeler (a free download), to show the thing type vs. things in question:
This diagram shows we have product types and part types, and each product type will require certain part types in its assembly. Each assembled product of a given product type will then include a part of the required part types. This gives us the basis needed to meet the remainder of the requirements.
All products have a job which dictates the product_pn(product part
number), the due date, and the qty to ship.
It sounds like products are ordered, and the assembly of those products to fill the order is a job. Since our goal in database design is to represent the entities of interest and not the processes being performed on them, there is no job table. When the requirement says the "job which dictates the product part number" this indicates the order which is for some quantity of a given product type to be produced by some date. So lets add the order to the design:
The design now shows an order may be made for a given quantity of products of a given type to be produced on a specified date.
A product failure is reported by a technician(any technician can
report a failure), and requires a description of the failure and what
operation the product failed at(inspection, programming, testing) as
well as a timestamp of when the failure was reported.
This requirement causes us to make a leap to recognize that a test was performed at a given date and time on each product by a technician who could either pass or fail the product. If the product fails the description of the failure and the test operation being performed when failure occurred is recorded. So now we add test to the model with test failure as a sub-type of test when the test results in a failure. The sub-type is not of product - for example product failure - as the product is not a "failure!" Instead, it failed the test. It can be repaired and be good as new. The test however either succeeds or fails and that never changes.
DMR Number is Redundant
A product is assigned a unique DMR number if it fails. DMR means the
product is discrepant material and should be quarantined until a
repair is made to determine if the product can be shipped. A given
product will never have more than one DMR number. The DMR number is
used to uniquely identify a given product to know what failures and
corresponding repairs have occurred(a person can enter the dmr number
to see the full history of failures and repairs for a given product).
At this point I think we need to question why a DMR number even exists. The requirement is confusing the product and the product type. When the requirement states "the product is discrepant material and should be quarantined until a repair is made to determine if the product can be shipped" it is talking about the product. When the requirement states "a product is assigned a unique DMR number if it fails" it is talking about the product type, as the desire is to know how many failures and repairs have occurred over time for that given product type. Given this, there is no need at all for such a number to be assigned as given the current design a simple query can be written to count the number of failed tests, and of those the number of successful repairs. In a sense, the model number itself is the DMR number and adding another DMR number to the product type adds nothing.
A repair requires a disposition(repaired, use-as-is, or scrap), a
repair code(process not followed, bad test equipment...), a
description of the repair, and who the qualified technician is that
repaired the board. We also need to capture any components that were
replaced on the product(many components could be replaced on a
product), and a timestamp the repair information was submitted.
If a product fails the test the requirement suggests a repair is always attempted, and it is attempted once. This means that while we have a repair process, we really don't have a new entity. Instead, we have additional data about the test telling us the result of the required repair attempt. We also know that, during the repair process some parts that were originally included in the assembly may have failed and they have been replaced with new parts. Now we would have a repair entity if the repair could be attempted multiple times, each with its own outcome, as now the relationship is one to many.
Let's add the test and repair tables to the model now along with a table to show that a given part in the product might be replaced by another part of the same type.
If a product is successfully repaired the product is assigned a unique
sn(serial number). The DMR number is linked to the sn and removed from
the product, so a history of all failures and repairs can be
maintained for any product. Some products will be scrapped and will
never receive a SN.
To meet this requirement we have a couple of choices. First, we could assign each product an assembly number, and then only if it passes do we assign that product - now certified for delivery - a serial number. This would require us to create a sub-type of product - say certified product - which has a serial number. Instead of doing this, I chose to make the leap to simply assigning a serial number to every assembled product. If that product later fails a test and can't be repaired, we still have a way to identify it - the serial number. We know for example the product given serial number 12345 failed its test, couldn't be repaired, and was scraped. This is a much simpler solution.
Thoughts on Keys
One topic I didn't address is selection of keys. I noticed in the schema a surrogate key, id, is used on some of the tables and it is not a good idea to do this with good justification. I made some assumptions in the design on what the keys to tables should be. Choosing the right keys for tables is a whole topic in and of itself which I didn't address. See the references at the end which address this in more detail.
Categorical Domains - Entities or Attributes?
I'm a bit unsure when to use an enum vs creating another table(e.g.
disposition can be scrap, repaired, or use-as-is). Am I on the right
That is a really good question. What you are struggling with is something I always struggle with in every database design - namely figuring out what is an entity and what is just an attribute - a property - of an entity. In this case, the attribute in question - repair disposition - has what I call a categorical domain, meaning its value can be chosen from a small, finite list of values. The approach I use when trying to decide if a data element with a categorical domain is actually an entity (and thus implemented as a table) or just an attribute of an entity (and thus implemented as a column with a check constraint) is if it has properties in its own right, or if it has relationships with more than one entity in the design.
Also, I'm planning on using MySQL innodb tables, any problems /
suggestions with this? This is for a small ajax based web app...
Thanks in advance.
This is an implementation question distinct from the decisions surrounding the database schema design. Once the schema design is complete you can choose the best DBMS for implementation. In this case I would say, if the application is small, any commercial DBMS will work just fine. If instead this were to be an extremely large application with thousands of simultaneous users and terabytes of data, then you would need to determine the characteristics of the workload and match that against your DBMS choices to see which afforded you the physical design capabilities, distinct from the database design schema, to meet the requirements of the workload. That is a wholly different exercise than logical databases design, and requires a really good DBA who is expert in each of the DBMS' you are considering to make a good decision.
Business data modeling and logical database design is a huge discipline that takes years of practice and a lot of study to start to master. A good starting point on the journey is Fabian Pascal's Practical Database Foundation Series which among other fundamentals addresses business modeling, which is at the conceptual level, translation of a business model into a database design at the logical level, and detailed treatments of domains and keys. A really good reference for business modeling at the conceptual level is David Hay's Enterprise Model Patterns. This text really distinguishes between things and types of things. Then, to really start on the journey of learning logical database design there are many excellent books by Chris Date and Hugh Darwen, Fabian Pascal, David McGoveran, a great write up on relational algebra by Erwin Smout as part of the SIRA_PRISE project, and of course Ted Codd. I hope this helps!