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.
As per my understanding, your business rules (requirements) are as follows.
Each User can have one or many User Friends.
Each User Friend must belongs to one User.
Each User can send one or many Messages.
Each Message is sent by one User.
By following the above mentioned business rules, your database design may looks like as follows,
USER
{
user_id
}
PASSWORD
{
password_id,
user_id
}
USER_FRIENDS
{
userfriend_id,
user_id,
added_by,
removed_by
}
MESSAGE
{
message_id,
message,
sent_by,
recieved_by,
is_read
}
If you need to track the user history, add the required data fields with domains such as timestamp, date, etc...at the appropriate entities (message history, login history, friends login, add/remove history). For calculating storage space and setting limit for users, you can done that using programing. I hope this will give you an idea.
Best Answer
What does
created
mean? When the record was created? If so, of what use is that? Perhaps it meanswhen_seen
(or rated)?I see no necessity to separate the two tables.
Since
seen
is not "optional", andrating
is, considerWhere the column is
NULL
until the user specifies a rating.NULL
has the advantage thatwill ignore the
NULL
values and get the 'correct' average.