[SOLVED] Diagram of database, normalized?

[hostfreak]

I have attached a diagram of a database I am working on. I am looking for opinions on whether or not it is normalized and if not suggestions to make it so. Suggestions about the overall design are welcome too. I know the "Parts; Part_Name, Part_#" could have there own table however, those will be user inputed (by the Mechanic) at all times. I should also note, this is my first time of actually trying to follow normalization on a database, so please forgive any newb mistakes. Thanks in advance.

 

[attachment deleted by admin]

[fenway]

Not sure about work_orders.counter....

[Barand]

... nor the final "overall_expenses" which looks like a derived aggregation of work_order_expenses

[hostfreak]

The counter is to generate the Key_ID. The count_num IS the Key_ID. I do this so I can insert a unique ID it into all relative tables upon adding. I couldn't figure out a way (if there is one) to add the "work_orders_main" ID into the other tables upon submission, being as the ID wouldn't have been generated yet as they are being submitted at the same time.

 

I created the "overall_expenses" to hold a total of all the relative total rows for a Work Order in "work_order_expenses". That way they can be displayed on a page that will only show the Overall Totals for a specific Work Order. Of course, I guess the rows could be pulled and totaled live, but this way IMO will save a little code work.

[fenway]

The counter is to generate the Key_ID. The count_num IS the Key_ID. I do this so I can insert a unique ID it into all relative tables upon adding. I couldn't figure out a way (if there is one) to add the "work_orders_main" ID into the other tables upon submission, being as the ID wouldn't have been generated yet as they are being submitted at the same time.

I don't know what you mean by "not being generated yet"... you can use last_insert_id() once you're made the primary id.

 

I created the "overall_expenses" to hold a total of all the relative total rows for a Work Order in "work_order_expenses". That way they can be displayed on a page that will only show the Overall Totals for a specific Work Order. Of course, I guess the rows could be pulled and totaled live, but this way IMO will save a little code work.

Storing summary data like this is usually a bad idea in a primary table...

[hostfreak]

I wasn't aware of the "last_insert_id()" function. So, even though data will be inserted at the same time to all tables, that will supply me with the primary ID of the entry entered into "work_orders_main"? If so, then that will eliminate the need for the key_id counter, which will be nice.

 

I'll do away with the "overall_expenses". So with changing these things, would you say it is normalized? Or close to?

[hostfreak]

I've looked into last_insert_id() and that will not work for me, as I have multiple INSERT queries. I would like the primary ID from the first INSERT query stored in all the tables, however with last_insert_id() it will return the ID from all my INSERT queries as all of them have auto-increment fields.

[Barand]

Insert new record into "work_orders_main", then

 

$new_wo_id = mysql_insert_id();

 

Now use that value as the foreign key when you insert into the related tables

[hostfreak]

Ah, I read about that, but when I looked it up in the manual I typed: http://php.net/mysql_insert_id() ... so I was thinking php5 only. However, now after typing: http://php.net/mysql_insert_id , I get what I am looking for. Thanks Barand.

[Barand]

There is still duplication of columns, such as the 3 cost columns and mechanic_id.

 

Also, why part_name and part_# ? Just the part code would be held, with the name in a separate parts table

[hostfreak]

Ah, yeah. I will need to get rid of those as well. I guess it would be better of then to always generate the totals live? I was storing them thinking it would save some code work etc, but seems that is a bad practice.

 

The mechanic_id, reason for that is to track the mechanic that added the specific what have you (work order, part etc). Thats probably not good either?

 

I don't quite understand what you mean by the parts thing. There could be 3 different parts with the same name, but each have a different part #.

[Barand]

Ok but there must be some differences between the parts (maybe different manufacturer/price) or they would be the same part#, so in a simplified example

[pre]

work_order_expense                                    part

 

id    part#    partname                              part#    partname          supplier    cost

----+--------+----------------+                      --------+----------------+-------------+--------

1  | 123    |  widget                                123    | widget        |    101      |  0.99

2  | 124    |  widget                                124    | widget        |    444      |  1.05

[/pre]

 

if you know the code, you can get its name. No need to hold it in the expense table because you get it from the parts table.

[Barand]

The mechanic_id raises a problem. What if there are two or more mechanics involved in the same work order?

Is the mechanic who raised the order the only one doing the work. Could one be, say, a junior mechanic with a different labour rate?

 

This where a knowledge of the data is essential. It's not possible just to look at a schema and say with certainty that it is correct without that knowledge. You need to question the data as above.

[hostfreak]

Oh,yeah I see on the parts now. I had actually changed that, so I was looking at an updated diagram. I got rid of part_name & part_# in work_orders_expenses. It will now only hold the part_id.

 

There will only be one mechanic ever doing the inputting. Then, for each expense row, he will input the initials of the mechanic(s) doing the work.