Potential schema for product failures and repairs

Question

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:

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.

Answer 1

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.

Answer 2

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.

Orders

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.

Tests

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.

Repairs

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.

Serial Numbers

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

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.

DBMS Selection

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.

References

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!