feelgood

I'm in the middle of a major school project that has been running from previous semester. We're in the design stage of a information systems which is probably a mickey mouse compared to the ones in the "real" world.

Anyways, my group and I are having disagreement regarding normalization of several database tables in MySQL.

Basically, what we have right now is 2 tables from the Conceptual ERD. They are Job Order and Product.

Job Order contains information about Products that are being made in the production line while Product contains information about products being sold as well parts needed to make the product. Parts can also be sold as a product.

Initially, after I reviewed the requirements regarding the Job Orders, I realized that there was no way for the user of the system to select part from a list of parts in the system and add it to the job order. Obviously, you need to have parts in order to make a complete product.

The original table looks like

{Job Order} >o-------------|- {Products}

This table was made based on that the Job Order table would contain the Product ID which is linked to Products.

When we factor in the fact that we need to have parts to be used for Job Orders. The table will look like this

{Job Order} >o-------------o< {Products}

This works fine in conceptual ERD but when we moved to implementation. The table would look like

{Job Order} >o------|- {Parts} -|-------o< {Products}

The justification for this was that parts table would contain Job Order ID, Product ID and Part ID. There is no product ID information in the job order table. The part id is actually the same information as product ID in the product table. I disagree with this.

I believe that, we don't actually need the product id in the parts table, but instead, put it in the Job Order table and have a Many to One relationship to the products table and keep everything else the same.

So, my idea will look like this:

{Job Order} >o------|- {Parts} -|-------o< {Products}
>o--------------------------|-

My main justification is that, if I wanted to know what the job order is making, why would I need to go through 2 different table (Parts -> Products) to find that out where I could simply just make a link directly to the products table.

My group member and course instructor is against me while I have my database instructor on my side.

What's your opinion?

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war