...

View Full Version : Database Normalization



kenwvs
09-02-2006, 04:26 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 - Update Remarks
Remarks, ID(Foreign Key)

guelphdad
09-04-2006, 06:55 AM
table 3 and table 4 need to be normalized.

table 3 should be
ID, upload

table 4 should be
id, part, description

if those fields have to specifically identify the part number or the upload order then you would add ONE additional column to those tables for that identification purpose. for instance in table 4 if you need to know that
id =444, part=1234565, description='flange' can only go in part #6 and not any other spot then you would have a column called whatpartlocation or something like that and add 6 to that field.

you should not have 14 columns to identify your parts and theri descriptions.

kenwvs
09-04-2006, 07:14 AM
I have changed table 3 and 4 (since this posting, but before your message) to accomodate what you suggested. The parts are not specific as the same part could be used in different engines, or different spots in an engine.

The problem I have now is I am not sure how to create a query that will INSERT the 7 parts number/descriptions into the DB when I don't have a column for each one to send it to.

Ken

guelphdad
09-04-2006, 02:39 PM
you would just send each part to the table as a row, if you have six parts, you'd have six rows.

show us what you are using for your layout and how you think the data should be inserted.

kenwvs
09-04-2006, 05:10 PM
Here is what I have, along with a couple of questions.

Questions first.

1. When using table normalization methods, which table do I enter the data into. I have a workorder form with data fields for employees. I have an employee table, along with a workorder_employee table. I am thinking I will enter it into the employee table, and then when I use the join statement it will be able to pull the data from various tables.

2. Can i create several mysql query's one after another, so that I can enter data into various tables? Do you create them all individually or do you somehow need to tie them together?

Here is the setup that I currently have, along with the main query for the majority of the data.

This is the form fields:


<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR>
<input type="text" size="10" maxlength="10" name="number[]">
<input type="text" size="93" maxlength="93" name="description[]"><BR></center>

This is the query to the point I have it so far.


mysql_query ("Insert into `workorder`(work, sched, site, serial, hours,
starts, issue, severity, resolution, assistance, safety) VALUES
('$work', '$sched', '$site', '$serial', '$hours', '$starts',
'$issue', '$severity', '$resolution', '$assistance', '$safety')");

mysql_query ("Insert into `employee` (name) VALUES ('$name')");

mysql_query ("Insert into `parts`(number, description)VALUES
($_POST['number'][0]
$_POST['number'][1]
$_POST['number'][2]
$_POST['number'][3]
$_POST['number'][4]
$_POST['number'][5]
$_POST['number'][6]
$_POST['description'][0]
$_POST['description'][1]
$_POST['description'][2]
$_POST['description'][3]
$_POST['description'][4]
$_POST['description'][5]
$_POST['description'][6])")
or die(mysql_error());

guelphdad
09-04-2006, 09:59 PM
what you want to do is use TRANSACTIONS you would enter each part of the relevant data into the correct data. If some info belongs in table A insert it there, then some in table B? insert that into table B.

If you wrap your inserts into a TRANSACTION then if only some of the data is inserted but something goes wrong, say your database goes down, the transaction is ROLLED BACK and you will then re-insert the data in your tables. that way you don't end up with some of the data inserted and not the remainder in the other tables.

Check the mysql manual for using TRANSACTIONS, note you will need to use innodb tables to support them correctly.

kenwvs
09-04-2006, 10:13 PM
Check the mysql manual for using TRANSACTIONS, note you will need to use innodb tables to support them correctly.

What do you suggest if my host doesn't support innodb tables? Is it still possible to use TRANSACTIONS

guelphdad
09-05-2006, 01:50 PM
then I would suggest you find another host.

kenwvs
09-05-2006, 02:34 PM
then I would suggest you find another host.

Since that isn't an option at this time, do you have any suggestions on how I proceed from here.

Beagle
09-05-2006, 03:46 PM
pray that you don't get burned. Transactions are an important part of data integrity. If you don't use them, and something goes wrong, you could get burned.

If you're willing to take the risk, proceed without transactions. They only help you in emergencies, their not necessary to make the process actually work.

guelphdad
09-05-2006, 04:06 PM
ask them why they don't support innodb table types? they are very simple to turn on and it doesn't take long. AFAIK there are no unacceptable risks with one over the other.

kenwvs
09-05-2006, 04:20 PM
I use LunarPages and I actually did ask them about Innodb when I first became aware of the need for this type of table and was told that they do not offer them, and will not be offereing them in the near future due to, from what I understood them to say, the high drain on system resources.

Needless to say, since I have about 10 months left with them, I need to move forward and do the best, with what I have.

Since I don't have access to Innodb, I am not able to use TRANSACTIONS, which leaves me at a risk IF I run into problems. I could end up having difficulty with my data if I have something go South on me.

Now that I know that I can't use TRANSACTIONS, how should I proceed to get this all working? I am thinking that I need to do a query that will put the data in the parent table, and then do I have to also do a query to tie it into the child tables, or will that happen since I have the workorder_parts table set up?

Ken

Fumigator
09-05-2006, 11:26 PM
I've told you before-- you'll need to write the query for each insert you need to do. It doesn't happen by itself.

kenwvs
09-05-2006, 11:42 PM
I've told you before-- you'll need to write the query for each insert you need to do. It doesn't happen by itself.

I am working on the query for this, but all the information about TRANSACTIONS started to worry me that I was on the wrong track. Didn't mean to sound like I wasn't listening to what you said.

Ken

guelphdad
09-06-2006, 01:08 AM
all transactions does Ken is ensure that if something goes wrong with an insert that you roll back so you don't have partial data inserted into one table and missing the related data in the second table. Since you aren't using them you won't have that safety net, but you still make your inserts separately to each table.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum