Mysql – Potential schema for product failures and repairs

database-designMySQL

I'm creating a schema for product failures and repairs and would appreciate feedback on the schema.

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.

All products have a job which dictates the product_pn(product part number), the due date, and the qty to ship. 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). 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.

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.

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.

Here is what I have for the schema so far:
enter image description here

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 track?

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.

Edit: Thanks for all your help so far. Sorry I'm new here so had to try to clarify the original post based on feedback.

Best Answer

Model

Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa.

Each table uses a surrogate primary key for clarity. This is not really necessary in several cases e.g. in RepairComponent, so feel free to replace it with a composite primary key.

The Product table stores a product. The field Product.pn (part number) should identify your product since it is manufactured; the product is then assigned a DMR when it fails and then, if it is repaired, a S/N.

Product
------------------
pn (PK)
DMR (IDX unique)
SN

Job table:

Job
------------------
id (PK)
Product.pn (FK)
due_date
quantity_to_ship

A product can fail multiple times and can be repaired multiple times (many to many relationship).

A product may fail multiple times but each repair concerns only one product, so it's a one-to-many relationship. You can hence have simply one table Repair (that stores a repair event) referring to Product.

Failure
------------------
id (PK)
Product.DMR (FK)
description
operation
date
Technician.id (FK)

Repair
------------------
id (PK)
Product.DMR (FK)
disposition
code
date
Technician.id (FK)

RepairComponent
------------------
id (PK)
component_pn
Repair.id (FK)

Technician
------------------
id (PK)
name

Enum vs table

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 track?

If you are dealing with an immutable set of operations (i.e. you're absolutely positive that Repair.disposition will always be one and only one of the options you listed) then it's ok to use an enum. Otherwise, your ER model will be much more flexible if you create another table:

DispositionTypes
------------------
id (PK)
description

as now you can create all kinds of new disposition types e.g. "Repaired", "Use as is", "Scrap", "Sell to China", or "Bury in landfill". In this case, you remove Repair.disposition and replace it with a FK DispositionTypes.id instead.

The advantage of this approach is that you can set up authorization levels for Technicians to perform a specific disposal (it is not mentioned in your question but it shows in your model, if I'm not wrong). This is done via a join table Authorizations:

Authorization
------------------
Technician.id      (PK) (FK)      
DispositionType.id (PK) (FK)

Same talk for RepairCode:

RepairCode
------------------
id (PK)
description

Personally I like to design databases that allow for flexibility, but the choice here it's up to you.

Choice of the DB engine

A MySQL database with InnoDB (which is the standard engine in MySQL) will work fine, so it's a good choice.

Related Question