Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normalized Table, but now what

    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!

    Ken

    Here is an example of the query I was using....

    Code:
    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',
    	'$Safety')")
    	or die(mysql_error());
    
    	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";

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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):
    PHP Code:
    $query "
    SELECT a.workorder_name,
            b.part_nr
    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.

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Ken

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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):
    Code:
    workorder table
    
    work_id    site           hours
    ---------------------------------
    1000       Park City      8
    1001       Centerville    2
    1002       Aurora         16
    
    
    employees table
    
    employee_id    name
    ----------------------------------
    2000           Bill Watkins
    2001           Henry Higgins
    2002           Lepreal Washington
    2003           Sonny Davis
    2004           Maria Sanchez
    2005           Pam Palore
    
    
    image table
    
    image_id       Upload
    ----------------------------------
    3000           img/bluetruck.jpg
    3001           img/house.jpg
    3002           img/tree.jpg
    
    
    parts table
    
    parts_id  parts_number  parts_description
    --------------------------------------------
    4000      A12399        Widget
    4001      B00119        Gadget
    4002      C02993        Wonkmack
    
    
    remarks table
    
    remarks_id   remarks
    -----------------------------------
    5000         Spendid work.
    5001         We're late on this one.
    5002         We don't have a Wonkmack.
    5003         Done.
    Now we need to use the xref tables to connect them all up.
    Code:
    workorder_employees table
    
    work_id      employee_id
    ----------------------------------
    1000         2004
    1001         2002
    1001         2004
    1001         2005
    1002         2003
    1002         2000
    1002         2005
    
    
    workorder_image table
    
    work_id      image_id
    -----------------------------------
    1000         3000
    1000         3001
    1001         3002
    1001         3000
    
    
    workorder_parts table
    
    work_id      parts_id
    -----------------------------------
    1000         4000
    1001         4000
    1001         4001
    1001         4002
    1002         4002
    
    
    workorder_remarks table
    -----------------------------------
    1000         5000
    1001         5001
    1001         5002
    1002         5003
    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:
    PHP Code:
    //select employees
    $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 

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

    EDIT:

    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.

    Thanks,

    Ken
    Last edited by kenwvs; 09-03-2006 at 01:21 AM.

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:

    workorder table
    work_id is the primary key

    employee table
    employee_id is the primary key

    workorder_employee table
    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

  • #7
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Ken


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •