Jump to content


Photo

Database Normalization


  • Please log in to reply
3 replies to this topic

#1 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 02 September 2006 - 03:25 PM

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)

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 September 2006 - 03:52 PM

Looks fine, except for the the hard-coded 4 uploads and 7 listings -- you should break these out into lookup tables.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 03 September 2006 - 12:09 AM

Lots of foreign keys but not a primary key in sight
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 03 September 2006 - 12:17 AM

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!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users