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

    Database Normalization

    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)

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

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

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

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

    Code:
    <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.

    PHP Code:
    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()); 

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #7
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad
    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

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    then I would suggest you find another host.

  • #9
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    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.

  • #10
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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.

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

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

  • #13
    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
    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.

  • #14
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    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

  • #15
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.


  •  

    Posting Permissions

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