Jump to content

Archived

This topic is now archived and is closed to further replies.

kenwvs

Database Normalization

Recommended Posts

I have worked through my database trying to seperate the fields out, to normalize my database.  Here are the various fields and tables, as I see they should be set up.  I would appreciate any assistance, in making sure that I have it set up in the most efficient manner.

I have a form set up where an employee will fill it out when an engine fails.  The pertinent information is filled out, including images, then a copy of the information is sent to other employees (selected in the form).  The Manager will then assign a purchase order to this work order, along with his comments.  The employee can then add remarks to update the work order and when the job is completed, he will include a final report including the total cost of repairs.

Table 1 - Work Order
Work Order # (generated using MMhhmmss format), Serial #, Hours on Unit, Site, Starts on Engine, Time Generated, Issue, Severity, Resolution, Safety, Assistance Required, Purchase Order #, Manager Comments,  ID (Foreign Key)

Table 2 - Employee
Name, Email Address, Password, Position (manager or technician), ID (Foreign Key)

Table 3 - Images
Upload 1, Upload 2, Upload 3, Upload 4, ID(Foreign Key)

Table 4 - Parts
Parts # 1, Parts Description 1, etc. through to room for seven listings, and ID (Foreign Key)

Table 5 - Comments
Update Remarks, ID(Foreign Key)

Share this post


Link to post
Share on other sites
Looks fine, except for the the hard-coded 4 uploads and 7 listings -- you should break these out into lookup tables.

Share this post


Link to post
Share on other sites
hmmmm, would I not use the fk as the primary key as well.  I have made all of the **_id columns my primary key.  Do I need to make a separate foreign key?

EDIT:  I HAVE THIS ISSUE FIGURED OUT AS FAR AS PRIMARY AND FOREIGN KEYS....THANKS ALL!!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.