View Full Version : Normalized Table, but now what
09-02-2006, 09:45 PM
I had got to the point where I had all of my data being saved into my Mysql DB, and then it was suggested that I normalize my DB. I have done that and have ended up with 5 tables and 4 additional tables to join the many->many tables. The tables I have are workorders, employees, image, parts and remarks. I have then created work_employees, work_image, work_parts, and work_remarks table to join them together. I am lost as how to create the query now that will get the data from the form, the data will end up in the proper DB table, and work for me to call it back up to review it in a form later.
My question is where am I going to want to send them to. Do they go to the actual table with all the fields, or do I send them to one of the join tables.... I have never done this before, as the query below was my first attempt with php and databases.
Thanks for the help!
Here is an example of the query I was using....
mysql_query ("Insert into `workorder`(ID,Work, Sched, Name, Site, Serial, Hours,
Starts, Issue, Severity, Resolution, Assistance, NumberA, PartsA, NumberB,
PartsB, NumberC, PartsC, NumberD, PartsD, NumberE, PartsE, NumberF, PartsF,
NumberG, PartsG, Safety) VALUES
('', '$Work', '$Sched', '$Name', '$Site', '$Serial', '$Hours', '$Starts',
'$Issue', '$Severity', '$Resolution', '$Assistance', '$NumberA', '$PartsA',
'$NumberB', '$PartsB', '$NumberC', '$PartsC', '$NumberD', '$PartsD',
'$NumberE', '$PartsE', '$NumberF', '$PartsF', '$NumberG', '$PartsG',
mysql_query ("Insert into `image`(Upload1, Upload2, Upload3, Upload4) VALUES
('$Uploads1', '$Uploads2', '$Uploads3', '$Uploads4')")or die(mysql_error());
echo "Work Order # '$Work' has been Updated Successfully";
09-03-2006, 12:12 AM
Nice job on the normalization! While it may seem like you created more work for yourself now, you'll be glad you did this later.
And now to get down to the "more work now" part. :) You need to write an insert query for each of your tables. You need to keep track of the primary key you insert, so you can use it in your other table inserts. If you are using the "auto-increment" feature on your IDs, you can find out the value of the ID you just inserted by calling the mysql_insert_id() function right after the insert query.
For selecting the data, you use a join. The easiest join syntax (for me anyway) is this shorthand (just an example):
$query = "
FROM workorder_tbl as a,
parts_tbl as b
WHERE a.workorder_id = b.workorder_id
AND a.workorder_id = .$id";
There are other ways to write a join, but it basically says "select the name out of the workorder table and the part number out of the parts table for every row you find in the parts table with a workorder ID that matches the variable $id".
If you want to post your specific table schemas, I can be more specific with column and table names, etc.
09-03-2006, 12:32 AM
Here are the table names and columns of my set up, Thanks for offering to give me an idea how to do this.
Table Name = workorder Column Names are work_id (fk), work, site, serial, hours, starts, sched, issue, severity, safety, resolution, assistance, purchase, comments.
Table Name = employees Column Names are employee_id (fk) name, email, password, manager
Table Name = image Column Names are image_id (fk) and Upload
Table Name = parts Column Names are parts_id (fk) parts_number and parts_description
Table Name = remarks Column Names are remarks_id (fk) and remarks
The names of my join tables are workorder_employees, workorder_image, workorder_parts, workorder_remarks
I am not sure what look up tables are, or how they work, but it is my understanding that the image, parts and remarks tables need to be of this sort, as the number of entries for the tables could vary from workorder to workorder.
It is my understanding (as basic as it is) that I don't need nor want any duplicate data in the tables, so I wouldn't put the common denominator which is the workorder number in any of the other tables. Not sure if this makes a difference, but the table type is myisam as my provider does not support innoDB
Thanks for the help
09-03-2006, 01:05 AM
Ah ok, you're using lookup tables. Not a problem. I've always called them cross-reference tables... they are simply the tables that store the connection (via primary keys) from one table to another.
Data will look like this (I've omitted columns for brevity):
work_id site hours
1000 Park City 8
1001 Centerville 2
1002 Aurora 16
2000 Bill Watkins
2001 Henry Higgins
2002 Lepreal Washington
2003 Sonny Davis
2004 Maria Sanchez
2005 Pam Palore
parts_id parts_number parts_description
4000 A12399 Widget
4001 B00119 Gadget
4002 C02993 Wonkmack
5000 Spendid work.
5001 We're late on this one.
5002 We don't have a Wonkmack.
Now we need to use the xref tables to connect them all up.
You use INSERT queries to insert rows into your xref tables when a workorder is created, and when remarks, images, etc. get added to a workorder.
Lastly, here's a really simple join query to get all of the employees for a particular work order:
$query = "
SELECT a.employee_id, a.name
FROM employees as a, workorder_employees as b
WHERE b.work_id = '$woID'
AND a.employee_id = b.employee_id
09-03-2006, 01:43 AM
Thank you for the break down, I have printed it out and will not study it and see if I can understand it. From the first reading, I have two questions.
1. You seemed surprised I was using look up (cross reference)tables. Is this the best way to go? If not, now is the time to change plans, as I have no data in the system, and am learning one way or the other.....don't want to change in the future, if I don't have to.
2. If I undestand correctly, when I create a query to input the data to a cross reference table, it will actually get saved to the appropriate table. As an example, if I save a workorder, and use a query to put the images to the work_image table, it will automatically put the data in the image table? Is this true, even if the database type isn't innodb?
I think I have this figured out (atleast a basic understanding) and have a feeling I have missed one important step. I have assigned (theoretically) a foreign key to each table, but have used the same key as the primary key. I am thinking I need to assign a separate foreign and primary key for each table.
09-03-2006, 08:25 AM
No I'm not surprised you're using lookup tables; you should, for some things. In the case of your workorder-to-employee releationship and your workorder-to-parts relationship, definitely use lookup tables. You'll be using the same employees and parts for any number of workorders, right? That's the question to ask.
The tables I wouldn't bother using lookup tables with are your remarks table and, perhaps, your images table. Will the remarks reference only one workorder, or will there be generic remarks that several workorders will reference? Are the images exclusive to one workorder, or are images shared between workorders?
If the answer is a remark is only used by one workorder, then you can put the work_id in the image table itself, and skip the lookup table.
To answer your point #2, nothing happens automatically (unless you set up triggers, but don't worry about that). You will be doing inserts into every table that needs a new row.
As for foreign keys: Remember what a foreign key is. It is simply a field in one table that refers to the primary key of another table. It looks as though you've labeled all of your primary keys "fk", which isn't quite right. For example:
work_id is the primary key
employee_id is the primary key
wo_emp_xref_id is the primary key
work_id is called a foreign key-- it stores a value that links the row to the workorder table
employee_id is also a foreign key-- it stores a value that links the row to the employee table
09-03-2006, 01:58 PM
Thank You very much for the clarification. It is starting to make sense, and you were correct that the remarks will only pertain to one workorder, whereas the images may be used on several workorders.
I am now working on the insert query to get the data into the tables when a workorder is completed and will let you know how I make out.